Sql query for displaying multidimensional data.

Question

Sql query for displaying multidimensional data.

Subhash-141882

Ten Centuries

Points: 1294
More actions
December 23, 2005 at 3:47 am

#96236

Hi There,
Can anyone help me for writing an SQL query for the below mentioned scenario:
I have data as mentioned below in a flat file format which can also be converted into Access or SQL database if needed:
Doc nb 706000 707000 411000
123 125 50 20
124 250 12 30
125 40 50
(Note:
             There are 3 different fields namely:
             Doc Numbers ( 123,124 & 125)
           Acccount Numbers (70600, 707000, 411000) and
           Amounts (125, 50, 30, 250 and so on...)
The above data is in PIVOT table (multidimensional) format.
Can i write an SQL query from the above data with which i can display the data as mentioned below:
Doc nb Account Amount
123 706000 125
123 707000 50
123 411000 20
124 706000 250
124 707000 12
124 411000 30
125 707000 40
125 411000 50
I prefer writing a query directly referring to a flat file (which seems to be quite difficult and tricky) as much as possible than converting into an Access or SQL table. If any one knew about the solution considering both the possible data formats, kindly help me.
Thanks in Advance,
Subhash
December 26, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

k_vasanthkumar Old Hand Points: 349 More actions · Answer 1

I think it would be better if you can create a DTS package to do the export from the flat file to Access/SQL server.Probably you can write an ActiveX script which would do the data manipuation in DTS...

This would be the simplest way to import data from Flat File to what ever database..

Regards,

Vasanth

RyanRandall SSChampion Points: 13623 More actions · Answer 2

Hi Subhash,

If you get your data into a SQL table, you can do something like the following (this SQL is safe to run):

create table #t (DocNumber int, c706000 int, c707000 int, c411000 int)

insert into #t values (123, 125, 50, 20)

insert into #t values (124, 250, 12, 30)

insert into #t values (125, null, 40, 50)

select DocNumber, 706000 as 'AccountNumber', c706000 as 'Amount' from #t where c706000 is not null

union

select DocNumber, 707000, c707000 from #t where c707000 is not null

union

select DocNumber, 411000, c411000 from #t where c411000 is not null

drop table #t

Using DTS somewhere in the solution as Vasanth suggests may also be a good option.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.