January 30, 2012 at 9:08 pm
0 down vote favorite
share [g+] share [fb] share [tw]
I have a table:
ID Name
-- -----
1 aaa
2 bbb
2 ccc
2 ddd
3 eee
3 fff
4 ggg
5 hhh
I need to pivot this table so that the resulting table looks like this:
ID Name1 Name2 Name3 Name4 Name5 Name6
-- ----- ----- ----- ----- ----- -----
1 aaa NULL NULL NULL NULL NULL
2 bbb ccc ddd NULL NULL NULL
3 eee fff NULL NULL NULL NULL
4 ggg NULL NULL NULL NULL NULL
5 hhh NULL NULL NULL NULL NULL
I have to use this on tables generated by XML Source in SSIS. Please help!
January 31, 2012 at 1:24 am
This CTE solved the problem:
CREATE TABLE TestPivot (ID int, Name nvarchar(50))
INSERT INTO TestPivot VALUES (1,'aaa')
INSERT INTO TestPivot VALUES (2,'bbb')
INSERT INTO TestPivot VALUES (2,'ccc')
INSERT INTO TestPivot VALUES (2,'ddd')
INSERT INTO TestPivot VALUES (3,'eee')
INSERT INTO TestPivot VALUES (3,'fff')
INSERT INTO TestPivot VALUES (4,'ggg')
INSERT INTO TestPivot VALUES (5,'hhh')
;WITH
CTE_TestPivot AS
(
SELECT TOP(100000) ID
,'Name' + CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) as CHAR) AS NamePosition
, Name
FROM TestPivot as tp
ORDER BY ID
)
SELECT ID,Name1,Name2,Name3,Name4,Name5
FROM CTE_TestPivot
PIVOT(MAX(Name) FOR NamePosition IN ([Name1],[Name2],[Name3],[Name4],[Name5])) as p;
Hope it helps!
January 31, 2012 at 1:49 am
Try this too:
SELECT
[Name1] = MAX(CASE WHEN tp.RowNum = 1 THEN tp.Name END),
[Name2] = MAX(CASE WHEN tp.RowNum = 2 THEN tp.Name END),
[Name3] = MAX(CASE WHEN tp.RowNum = 3 THEN tp.Name END),
[Name4] = MAX(CASE WHEN tp.RowNum = 4 THEN tp.Name END),
[Name5] = MAX(CASE WHEN tp.RowNum = 5 THEN tp.Name END)
FROM
(
SELECT *, RowNum = ROW_NUMBER() OVER (PARTITION BY tp.ID ORDER BY tp.Name)
FROM dbo.TestPivot AS tp
) AS tp
GROUP BY tp.ID
ORDER BY tp.ID;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply