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..
32,324 total views, 3 views today