How to make a dynamic PIVOT on multiple columns
USE tempdb
CREATE
TABLE
tblTest (
Id
INT
,Col_1
)
INSERT
INTO
tblTest
VALUES
(
1
,12345
,(
,23456
,45678
2
,57823
,11111
,34304
,12344
DECLARE
@MaxCount
;
SELECT
@MaxCount =
max
(cnt)
FROM
,
count
(Col_1)
AS
cnt
GROUP
BY
) X;
@SQL NVARCHAR(
,@i
SET
@i = 0;
@SQL =
''
WHILE @i < @MaxCount
BEGIN
@i = @i + 1;
@SQL = @Sql +
',
MAX(CASE WHEN RowNo = '
+
cast
(@i
NVARCHAR(10)) +
' THEN Col_1 END) AS Col'
NVARCHAR(10));
END
@SQL = N
';WITH CTE AS (
SELECT ID, Col_1, row_number() OVER (PARTITION BY ID ORDER BY Col_1) AS rowno
FROM tblTest
SELECT ID '
+ @SQL + N
'
FROM CTE
GROUP BY ID'
PRINT @SQL;
EXECUTE
(@SQL);
COALESCE
(@Sql +
', '
) +
'Col'
SELECT ID, Col_1, '
' + CAST(row_number() OVER (PARTITION BY ID ORDER BY Col_1) AS Varchar(10)) AS RowNo
SELECT *
PIVOT (MAX(Col_1) FOR RowNo IN ('
')) pvt'
Naomi N edited Revision 19. Comment: Added one more link to a recent thread
Ed Price - MSFT edited Revision 7. Comment: Making the technology more obvious in the title.
Naomi N edited Revision 6. Comment: Adjusted participation link
Naomi N edited Revision 4. Comment: Added true PIVOT syntax
Naomi N edited Revision 3. Comment: Explanation of solution
Naomi N edited Original. Comment: More content
There should be an opportunity to share article on Google+
Naomi, there is a feature request we have logged about that. Thanks!
Naomi,
Congratulations on winning the May TechNet Guru contest! blogs.technet.com/.../technet-guru-awards-may-2013.aspx
Your article was featured here: blogs.msdn.com/.../t-sql-guru-dynamic-pivot-on-multiple-columns.aspx
Thank you for all your contributions!
Congratulations on being featured on the home page of TechNet Wiki!
social.technet.microsoft.com/.../default.aspx
And featured on the Wiki Ninjas blog: blogs.technet.com/.../may-t-sql-guru-sql-server-pivot-amp-dynamic-pivot-on-multiple-columns.aspx
Naomi N edited Revision 15. Comment: Minor edit
Naomi N edited Revision 16. Comment: Link to forum
Hi Naomi, You have helped out in the past, and when i found this and saw you developed it, i was really pleased, you are clever.