Convert table into a crosstab table in SQL Server

Question

Post reply

Convert table into a crosstab table in SQL Server

Raj Malwade

Mr or Mrs. 500

Points: 594
More actions
July 19, 2004 at 5:55 pm

#59840

Hi:
I am using DTS to load data in the data warehouse. I have a source table in this format.
ITEM                   SOURCE
a                          aa
a                          bb
b                          cc
b                          dd
b                          ee
I want to load the data in the destination table in this format.
ITEM                       SOURCE1                  SOURCE2              SOURCE3
a                              aa                             bb                       NULL
b                              cc                            dd                         ee

How do I achieve this using DTS ? The source table can have upto 4 source values for each ITEM.
Thanks for your help.

Viewing 7 posts - 1 through 6 (of 6 total)

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

JourneymanProgrammer SSCommitted Points: 1546 More actions · Answer 1

hey,

try to see if this is what you are looking for:

INSERT INTO Destination_Table(item, source1, source2, source3, source4)

SELECT

s1.item,

source1 = MAX(s1.source),

source2 = MAX(CASE WHEN s2.source <> s1.source THEN s2.source ELSE NULL END),

source3 = MAX(CASE WHEN s3.source <> s2.source AND s3.source <> s1.source THEN s3.source ELSE NULL END),

source4 = MAX(CASE WHEN s4.source <> s3.source AND s4.source <> s2.source AND s4.source <> s1.source THEN s4.source ELSE NULL END)

FROM

Source_Table s1 INNER JOIN

Source_Table s2 INNER JOIN

Source_Table s3 INNER JOIN Source_Table s4 ON s3.item = s4.item

ON s2.item = s3.item

ON s1.item = s2.item

GROUP BY

s1.item

JP

Raj Malwade Mr or Mrs. 500 Points: 594 More actions · Answer 2

HI: Thanks for your help. But with the query you have specified the

result I get is:

ITEM SOUCE1 SOURCE2 SOURCE3 SOURCE4

a bb bb bb bb

b ee ee ee ee

The result I am expecting is :

ITEM SOURCE1 SOURCE2 SOURCE3 SOURCE4

a aa bb NULL NULL

b cc dd ee NULL

Thanks for your help.

JourneymanProgrammer SSCommitted Points: 1546 More actions · Answer 3

try this one then:

INSERT INTO Destination_Table(item, source1, source2, source3, source4)

SELECT

DISTINCT s.item,

source1 = MIN(s.source1),

source2 = MAX(s.source2),

source3 = MAX(s.source3),

source4 = MAX(s.source4)

FROM

(

SELECT

s1.item,

source1 = s1.source,

source2 = MAX(CASE WHEN s2.source <> s1.source THEN s2.source ELSE NULL END),

source3 = MAX(CASE WHEN s3.source <> s2.source AND s3.source <> s1.source THEN s3.source ELSE NULL END),

source4 = MAX(CASE WHEN s4.source <> s3.source AND s4.source <> s2.source AND s4.source <> s1.source THEN s4.source ELSE NULL END)

FROM

Source_Table s1 INNER JOIN

Source_Table s2 INNER JOIN

Source_Table s3 INNER JOIN Source_Table s4 ON s3.item = s4.item

ON s2.item = s3.item

ON s1.item = s2.item

GROUP BY

s1.item, s1.source

) s

GROUP BY s.item

Hope this helps ....

JP

sql_servant Old Hand Points: 315 More actions · Answer 4

create table #Source_Table (ITEM char(1), SOURCE char(2) )

insert into #Source_Table

select 'a', 'aa' union all

select 'a', 'bb' union all

select 'b', 'cc' union all

select 'b', 'dd' union all

select 'b', 'ee'

select ITEM,

max(case sourceCount when 1 then SOURCE else cast(NULL as char(2)) end)

as SOURCE1,

max(case sourceCount when 2 then SOURCE else cast(NULL as char(2)) end)

as SOURCE2,

max(case sourceCount when 3 then SOURCE else cast(NULL as char(2)) end)

as SOURCE3

from ( select ITEM,

SOURCE,

(select count(distinct SOURCE)

from #Source_Table s2

where s2.ITEM = s1.ITEM and s2.SOURCE <= s1.SOURCE

)

as sourceCount

from #Source_Table s1

) mySources

group by ITEM

...with any luck, that statement should have been reformatted into oblivion...

Your life will be much more pleasant if you have a UNIQUE constraint on the pair (ITEM, SOURCE).

I did a trick similar to this with 30 pivots and 20,000 rows and... this will not scale infinitely.

Chris Hofland

Nivedita Sundar.N Mr or Mrs. 500 Points: 566 More actions · Answer 5

Dear Mr.Raj,

The below solution is not the best solution available. But it certainly produces the desired results when the max source values for each item is 4.

insert into destination_table(item, source1, source2, source3, source4)

select distinct

item,

(select top 1 source from table1 b where a.item=b.item) as Source1 ,

(select top 1 source from table1 b where a.item=b.item and b.source

not in (select top 1 source from table1 c where a.item=c.item) ) as Source2 ,

(select top 1 source from table1 b where a.item=b.item and b.source

not in (select top 2 source from table1 c where a.item=c.item) ) as Source3,

(select top 1 source from table1 b where a.item=b.item and b.source

not in (select top 3 source from table1 c where a.item=c.item) ) as Source4

from table1 a

Hope this helps.

Have a great day

Nivedita

Raj Malwade Mr or Mrs. 500 Points: 594 More actions · Answer 6

Nivedita:

This query does work. Thanks for your help.