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 pivot table, means you do not need to pass hard coded column names that you want to display in your pivot table.

Dynamic pivot query will fetch a value for column names from table and creates a dynamic columns name list for pivot 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 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 columnar data using PIVOT, means unique subjects values will become a column for pivot table, so you can see a individual student row-wise data along with the marks they have secured in each subject which is 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 transfer row- level data into columnar data using pivot, that is fine but the real challenge comes when in future one more subject is added in the subject table and our query supposed to be displayed the data for that subject as well but we have hard coded the subjects columns in our query.

Then in this case again we have to modify our pivot query to display that new added subject data in query output, Other wise it will keep displaying data only for those four subjects ..

So to avoid such issue we can create a dynamic pivot query, In this query we will 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 do not need to worry about that as dynamic column list will add this 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 passed any hard coded subject names for table columns. It is fetched automatically from subject table.

Lets add one more column in subject table to see whether it display that new subject in query output or not.

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

Lets execute the same dynamic pivot query again.

As you can see, new subject automatically is added in a query output. Value for this column is returned null as student do not have any marks for this subject yet.

So using dynamic pivot query you can reduce effort by modifying query again and again for displaying new column data in result set also no need to pass hard coded values for columns.

Also Read..

SQL Server Pivot

SQL Server Unpivot

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 32,324 total views,  3 views today