Convert table into a crosstab table in SQL Server
-
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.
-
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
-
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.
-
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
-
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 -
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
-
Nivedita:
This query does work.
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