Friday, June 28, 2013

Pivot in Sql

CREATE TABLE #tblitems(
  class    nVARCHAR(100),
  Subject  nVARCHAR(100),
  Total    INT
)

INSERT INTO #tblitems
SELECT 'Standard -12', 'Chemistry', 20 UNION ALL
SELECT 'Standard -11', 'Maths', 45 UNION ALL
SELECT 'Standard -12', 'Maths', 0 UNION ALL
SELECT 'Standard -11', 'Chemistry', 5 UNION ALL
SELECT 'Standard -12', 'Physics', 30 UNION ALL
SELECT 'Standard -11', 'Physics', 45 UNION ALL
SELECT 'Standard -11', 'Biology', 50 UNION ALL
SELECT 'Standard -12', 'Biology', 94

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

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT class, ' + @paramList
+ ' FROM( SELECT * FROM #tblitems )src
PIVOT(SUM(Total) FOR Subject IN (' + @paramList + ')) pvt order by class desc'

EXEC sp_executesql @query
DROP TABLE #tblitems