Prev: varbinary(max) field in sql server 2005 problems
Next: Varbinary - Word Documents
From: Frank on 12 Mar 2010 07:47
Hi,

I have the data from a flattened spreadsheet in a table in the
following form:

f1 f2 f3
period to: Scheme1 Scheme2
31/01/2005 Net Gross
28/02/2005 Net Gross
31/03/2005 Net Gross
30/04/2005 Net Gross
31/05/2005 Net Gross
30/06/2005 Net Gross
31/07/2005 Net Gross
31/08/2005 Net Gross
30/09/2005 Net Gross
31/10/2005 Net Gross
30/11/2005 Net Gross
31/12/2005 Net Gross

f1, f2 and f3 are the column names, the rest is data.
And there are 120 other columns of similar data to columns f2 and f3.
What I want to do is join this table to another table using the scheme
name.
The problem that I have is that in the table above the scheme name is
not the name of the column, it is just part of the data.
And I have no idea up front which scheme each of the 'f' column names
will map to i.e. whether f2 will equal Scheme1 etc.
Is there an idiomatic way of transforming this data into a usable
state?

Many thanks,
Frank.
From: Stefan Hoffmann on 12 Mar 2010 07:58
hi Frank,

On 12.03.2010 13:47, Frank wrote:
> f1, f2 and f3 are the column names, the rest is data.
So 'period to:', 'Scheme1' and 'Scheme2' is data?

> The problem that I have is that in the table above the scheme name is
> not the name of the column, it is just part of the data.
This is the normal way a JOIN works. So where is the problem?

SELECT *
FROM theAboveTable A
INNER JOIN otherTable B
ON A.f2 = B.schemeField

> And I have no idea up front which scheme each of the 'f' column names
> will map to i.e. whether f2 will equal Scheme1 etc.
What do you mean?

> Is there an idiomatic way of transforming this data into a usable
> state?
Maybe, your explanation is quite mysterious...


mfG
--> stefan <--
From: Frank on 12 Mar 2010 08:32
Hi Stefan,

> Maybe, your explanation is quite mysterious...

Yes, apologies for that. Let me explain further...

Yes, 'period to:', 'Scheme1' and 'Scheme2' is data. 'f1', 'f2', 'f3'
etc are column names.
The problem that I have is that I do not know which column will hold
the value 'Scheme1'.
It could be any one of the columns from f2 through to f120.
And the only reason that I want to get to 'Scheme1' is to find out the
values in the rows underneath it (i.e. Net, Gross and dates etc).

I was hoping that by turning the scheme name into a column (or even a
row under a single column) I could then query it more easily to find
the other values.

Regards,
Frank.
From: Stefan Hoffmann on 12 Mar 2010 09:00
hi Frank,

On 12.03.2010 14:32, Frank wrote:
> Yes, 'period to:', 'Scheme1' and 'Scheme2' is data. 'f1', 'f2', 'f3'
> etc are column names.
> The problem that I have is that I do not know which column will hold
> the value 'Scheme1'.
If your importing from Excel, use the header option:

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\yourExcel.xls;HDR=Yes',
'SELECT * FROM [yourSheet$]');


mfG
--> stefan <--

From: Frank on 12 Mar 2010 09:53
Hi Stefan,

Unfortunately, it's a multi-sheet file being flattened by a macro to
a .csv file first before being imported using SSIS.
It may be possible to save the header within the macro, but I was
hoping not to have to touch that part of the development.
Once again, thanks for your help.

Regards,
Frank.
 |  Next  |  Last
Pages: 1 2
Prev: varbinary(max) field in sql server 2005 problems
Next: Varbinary - Word Documents