DYNAMIC PIVOT
My previous PIVOT UNPIVOT post describes to organize data in a cross tab fashion or to transpose columns into rows and vice-versa.
This is fine only when you know how many columns you need. But when columns vary according to the query then how could you apply them on your scripts. The only way is to store them in a string variable at runtime and apply them in a dynamic SQL query, shown below.
This problem was also discussed on MSDN’s following link:https://social.msdn.microsoft.com/Forums/en/transactsql/thread/26f86fd6-6d06-4cc5-9723-9ee4685bd48a where I proposed an answer also described below:
USE [tempdb] GO -- Create test tables create table #studMarks ( studId int, subjekt varchar(20), marks int ) -- Insert test data insert into #studMarks values (1, 'math', 90) insert into #studMarks values (1, 'science', 80) insert into #studMarks values (1, 'english', 70) insert into #studMarks values (1, 'history', 60) insert into #studMarks values (2, 'math', 80) insert into #studMarks values (2, 'science', 60) insert into #studMarks values (2, 'english', 90) insert into #studMarks values (2, 'history', 70) -- Static PIVOT select StudId, [math], [science], [english], [history] from (select StudId, subjekt, marks from #StudMarks)p PIVOT(MAX (marks) FOR subjekt IN ([math], [science], [english], [history]) ) AS pvt ORDER BY StudId
Output:
| StudId | math | science | english | history |
| 1 | 90 | 80 | 70 | 60 |
| 2 | 80 | 60 | 90 | 70 |
- Dynamic PIVOT:
-- Lets add one more record on both the tables to check the results insert into #studMarks values (1, 'economics', 95) insert into #studMarks values (2, 'economics', 85) declare @col varchar(1000) declare @sql varchar(2000) select @col = COALESCE(@col + ', ','') + QUOTENAME(subjekt) from (select distinct subjekt as subjekt from #StudMarks) K --select @col set @sql = ' select StudId, ' + @col + ' from (select StudId, subjekt, marks from #StudMarks)p PIVOT(MAX (marks) FOR subjekt IN ( ' + @col + ' ) ) AS pvt ORDER BY StudId' -- print @sql exec (@sql)
Output:
| StudId | economics | english | history | math | science |
| 1 | 95 | 70 | 60 | 90 | 80 |
| 2 | 85 | 90 | 70 | 80 | 60 |
So, whenever any new subject & marks are added in the table against any student the above Dynamic PIVOT statement will take care of it and add a new column for it.
- Final Cleanup
drop table #studMarks
Source: from my personal blog SQLwithManoj: https://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/