Created
August 21, 2014 03:38
-
-
Save AsifMushtaq/acb0d15ce85832fbba04 to your computer and use it in GitHub Desktop.
Dynamic Pivot
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| create table temp | |
| ( | |
| date datetime, | |
| category varchar(3), | |
| amount money | |
| ) | |
| insert into temp values ('1/1/2012', 'ABC', 1000.00) | |
| insert into temp values ('2/1/2012', 'DEF', 500.00) | |
| insert into temp values ('2/1/2012', 'GHI', 800.00) | |
| insert into temp values ('2/10/2012', 'DEF', 700.00) | |
| insert into temp values ('3/1/2012', 'ABC', 1100.00) | |
| DECLARE @cols AS NVARCHAR(MAX), | |
| @query AS NVARCHAR(MAX); | |
| SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) | |
| FROM temp c | |
| FOR XML PATH(''), TYPE | |
| ).value('.', 'NVARCHAR(MAX)') | |
| ,1,1,'') | |
| set @query = 'SELECT date, ' + @cols + ' from | |
| ( | |
| select date | |
| , amount | |
| , category | |
| from temp | |
| ) x | |
| pivot | |
| ( | |
| max(amount) | |
| for category in (' + @cols + ') | |
| ) p ' | |
| execute(@query) | |
| drop table temp |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment