Home » , » dynamic pivot in sql server

dynamic pivot in sql server

Penulis : Sandeep Mittal on Friday, November 18, 2011 | 11/18/2011 10:39:00 AM

Pivot in SQL Server is used to transform row values of a column as the columns of the output.

What is the need of dynamic pivot? 

When the row values of the column on which you are going to pivot are static i.e you already know what all set of values exist in the column, then static pivot would work for you. The need of dynamic pivot arises when the row values for the column are not static i.e you do not know what all values would be there in the column

CREATE TABLE #tblitems(
  item    VARCHAR(10),
  parameter  VARCHAR(10),
  value    INT
)

INSERT INTO #tblitems 
SELECT 'item1', 'param1', 10 UNION ALL 
SELECT 'item1', 'param2', 20 UNION ALL 
SELECT 'item1', 'param3', 30 UNION ALL 
SELECT 'item2', 'param1', 15 UNION ALL 
SELECT 'item2', 'param2', 20

-- Creating a list of all distinct row values 
-- that would be going to become columns
DECLARE @paramList VARCHAR(MAX)
SET @paramList = STUFF((
                    SELECT DISTINCT ',[' + parameter + ']' 
                     FROM #tblitems FOR XML PATH('')
                    )
                 ,1,1,'')
PRINT @paramList
-- OUTPUT : [param1],[param2],[param3]

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT item, ' + @paramList
+ ' FROM( SELECT * FROM #tblitems )src 
PIVOT(SUM(value) FOR parameter IN (' + @paramList + ')) pvt'

EXEC sp_executesql @query
DROP TABLE #tblitems
OUTPUT
Share this article :

+ comments + 2 comments

December 21, 2011 at 4:31 PM

Hi Sandeep,

Your Dynamic Pivot is exactly which meets my requirement. Please Explain it form Declare keyword. Your Post is really helpful.

Thanks
Shwetamber Chourey

anjali
October 5, 2012 at 10:03 PM

Thanks for the post

Post a Comment