Sunday, October 21, 2012

SQL Server - Dynamic Pivoting

 --Example to get pivoting columns dynamically

CREATE  TABLE TMP(DBName NVARCHAR(30),ID INT)
INSERT INTO TMP SELECT 'SQL Server',1
INSERT INTO TMP SELECT 'Oracle',2

--Example for dynamic pivot

DECLARE @Columns NVARCHAR(MAX)
        ,@query NVARCHAR(MAX)
SELECT @Columns = STUFF(
 (SELECT  ', ' +'['+DBName+']' FROM
 (SELECT DBName FROM TMP ) AS T FOR XML PATH('')),1,2,'')
  
SET @query = N'
SELECT ' + @Columns + '
FROM
(
  SELECT DBName,ID FROM TMP
)  i
PIVOT
(
  MAX(ID) FOR DBName IN ('
  + @Columns
  + ')
)  j;';
EXEC sp_executesql @query;



--adding 1 more row & checking dynamic pivoting

INSERT INTO TMP SELECT 'DB2',3

--Dynamic pivot

DECLARE @Columns NVARCHAR(MAX)
        ,@query NVARCHAR(MAX)
SELECT @Columns = STUFF(
 (SELECT  ', ' +'['+DBName+']' FROM
 (SELECT DBName FROM TMP ) AS T FOR XML PATH('')),1,2,'')
  
SET @query = N'
SELECT ' + @Columns + '
FROM
(
  SELECT DBName,ID FROM TMP
)  i
PIVOT
(
  MAX(ID) FOR DBName IN ('
  + @Columns
  + ')
)  j;';
EXEC sp_executesql @query;








 

No comments: