Advanced SQL - Dynamic Pivot
If you have any questions please ask me @ [email protected]
Here is the complete script-
---------------DYNAMIC PIVOT QUERY-----------------------------------
CREATE TABLE #LearnPivot (Student VARCHAR(100), English INT, Maths INT, Physics INT)
GO
INSERT INTO #LearnPivot VALUES ('James',90,80, 70), ('Philip', 80, 80, 80), ('Andrew', 89, 91, 20), ('Cooper', 78, 89, 78),('Donald', 80, 70, 70), ('Sushil',90,80,80), ('Mathew',20,90,90)
GO
SELECT * FROM #learnpivot
GO
Declare @Students Varchar(8000), @ColName Varchar(8000),@SQL Nvarchar(4000)
-- Extract Student Names in Run Time
SELECT @Students = STUFF(
(SELECT distinct ',' + Student
FROM #learnpivot
FOR XML PATH (''))
, 1, 1, '') from #learnpivot t2
PRINT @Students
--- Pivot the data based on currencies available and get the fund totals (RC value)
SET @SQL = '
SELECT '+'''Maths'''+' AS Subject'+', '+ @Students+' FROM
(
SELECT Maths,Student
FROM
#learnpivot
) t
PIVOT(
SUM(Maths)
FOR Student IN (
'
+
@Students
+
'
)
) AS pivot_table'
SET @SQL = @SQL +'
UNION
SELECT '+'''Physics'''+' AS Subject'+', '+ @Students+' FROM
(
SELECT Physics,Student
FROM
#learnpivot
) t
PIVOT(
SUM(Physics)
FOR Student IN (
'
+
@Students
+
'
)
) AS pivot_table'
SET @SQL = @SQL +'
UNION
SELECT '+'''English'''+' AS Subject'+', '+ @Students+' FROM
(
SELECT English,Student
FROM
#learnpivot
) t
PIVOT(
SUM(English)
FOR Student IN (
'
+
@Students
+
'
)
) AS pivot_table;'
PRINT @SQL
EXEC sp_executesql @SQL
DROP TABLE #LearnPivot