Nov 18, 2011

Dynamic Pivot in SQL Server

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
SET @paramList = STUFF((
                     SELECT DISTINCT ',[' + parameter + ']'
                     FROM #tblitems FOR XML PATH('')
PRINT @paramList
-- OUTPUT : [param1],[param2],[param3]

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

    Choose :
  • OR
  • To comment
Write Comments
  1. Hi Sandeep,

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

    Shwetamber Chourey