SQL Server Dynamic PIVOT

SQL server allows us to transform a row- level data into a columnar data using SQL Pivot.

You can also create a dynamic pivot query, which uses a dynamic columns for your pivot table. It means you do not need to provide a hard coded column names to your pivot table.

In a dynamic pivot query, column name list is fetched from table. So, whatever the values for column name will be in a table are considered as a columns for pivot table.

Lets see how a dynamic pivot query is used to display a dynamic columns in pivot table.

First, we create a two sample tables named as Subjects and StudentMarks.

CREATE TABLE Subjects(SubjectID INT, 
SubjectName VARCHAR(25)

CREATE TABLE StudentMarks(
StudID INT NOT NULL ,
SubjectID INT,
Score INT
)

Now we insert some records into Subjects and StudentMarks tables.

INSERT INTO Subjects (SubjectID, SubjectName) VALUES (51,'MATH'),
(52,'PHYSICS'),
(53,'SCIENCE'),
(54,'ART')

INSERT INTO StudentMarks(StudID, SubjectID, Score)
VALUES(1, 51, 400),(1, 52, 350),(1, 53, 499),(1, 54, 403),
(2, 51, 410),(2, 52, 390),(2, 53, 477),(2, 54, 411),
(3, 51, 250),(3, 52, 300),(3, 53, 488),(3, 54, 476),
(4, 51, 288),(4, 52, 259),(4, 53, 399),(4, 54, 400)

Lets, see the details of students and marks secured by student in each subjects.

Following query returns a student details along with the marks secured by an individual student in each subjects.

SELECT SM.StudID, S.SubjectName, SM.Score 
FROM StudentMarks SM 
INNER JOIN Subjects S
ON Sm.SubjectID = S.SubjectID

Lets transform this output into a columnar data using PIVOT, means unique subjects values will become a column for pivot table.

You can see a individual student row-wise data along with the marks secured in each subject are displayed in columns.

SELECT StudID , ISNULL([MATH],0) AS [MATH] , ISNULL([PHYSICS],0) AS  [PHYSICS], 
ISNULL([SCIENCE],0) AS [SCIENCE], ISNULL([ART],0) AS  [ART]
FROM
(
SELECT SM.StudID, S.SubjectName, SM.Score 
FROM StudentMarks SM 
INNER JOIN Subjects S
ON Sm.SubjectID = S.SubjectID
) AS tbl
PIVOT 
( SUM(Score)
FOR SubjectName IN ([MATH], [PHYSICS] ,[SCIENCE],[ART]) ) AS pvt

Now you can see all the subjects is displaying in table column .

Implementing Dynamic Pivot table

As we have seen how to transform row- level data into columnar data using pivot which is absolutely fine but the real challenge comes when in future one more subject is added to the subject table and we expect from our pivot query to display the data for that newly added subject but it does not display because we have hard coded the subjects columns in our pivot query.

So, to avoid this situation we implement dynamic pivoting. Let’s modify pivot query to display that new added subject data in query output, other wise it keeps on displaying the data only for those four hard coded subjects.

Now, we create a dynamic column name list that will directly fetch unique subjects name from subject table and create a dynamic column name list for pivot table.

So, whenever a new subject is added in subject table, we no need to worry about that as dynamic column list will add that subject in pivot query automatically.

DECLARE @colnameList varchar(200)
SET @colnameList = NULL
SELECT @colnameList = COALESCE(@colnameList + ',','') + SubjectName
FROM Subjects;
DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery =
'SELECT StudID , '+@colnameList+'
FROM
( SELECT SM.StudID, S.SubjectName, SM.Score 
FROM StudentMarks SM 
INNER JOIN Subjects S
ON Sm.SubjectID = S.SubjectID
) AS tbl
PIVOT 
( Sum(Score)
FOR SubjectName IN ('+@colnameList+') ) as pvt'

EXEC(@SQLQuery)

 

As you can see, this time we have not provided any hard coded subject for pivot table columns and it is fetched automatically from subject table.

Lets, add one more column in subject table to see whether it displays that newly added subject in query’s output or not.

INSERT INTO Subjects (SubjectID, SubjectName)
VALUES (55, 'BIOLOGY')

Lets execute the same dynamic pivot query again.

As you can see, newly added subject is added in a query output automatically.

Value for this column is returned null as student do not have any marks for this subject yet.

So, using a dynamic pivot query you can reduce an effort of modifying pivot query again and again to displaying a newly added column’s data in result set also no need to pass hard coded values for columns.

Also Read..

SQL Server Pivot

SQL Server Unpivot