Created
October 22, 2015 14:02
-
-
Save codingbadger/e480e314e0799597a7ff to your computer and use it in GitHub Desktop.
Dynamic Pivot SQL
This file contains 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 dbo.ProductInfo | |
| ( | |
| ProductName varchar(10), | |
| DateOfSale datetime, | |
| SaleValue int | |
| ); | |
| Insert Into dbo.ProductInfo | |
| Values ('a','2011-05-14',2), | |
| ('b','2011-05-14',4), | |
| ('c','2011-05-17',3), | |
| ('a','2011-05-19',6), | |
| ('a','2011-05-24',4), | |
| ('a','2011-05-25',7), | |
| ('a','2011-05-29',6); | |
| Declare @cols nvarchar(max), | |
| @SelectCols nvarchar(max), | |
| @sql nvarchar(max), | |
| @StartDate Datetime, | |
| @EndDate DateTime | |
| -- Set the date range here | |
| Set @StartDate = '2011-05-01' | |
| Set @EndDate = '2012-01-01' | |
| -- Build up the Pivot colummns using the data from the source table | |
| select @cols = coalesce(@cols + N',', N'') + | |
| QUOTENAME(Left(Convert(varchar, DATEADD(dd, -(DATEPART(dw, DateOfSale)-1), DateOfSale),120),10)) | |
| From dbo.ProductInfo | |
| Where DateOfSale >= @StartDate | |
| And DateOfSale <= @EndDate | |
| Group By DATEADD(dd, -(DATEPART(dw, DateOfSale)-1), DateOfSale) | |
| -- Build up the Pivot colummns using the data from the source table | |
| select @SelectCols = coalesce(@SelectCols + N',', N'') + N'Sum(IsNull(' + | |
| QUOTENAME(Left(Convert(varchar, DATEADD(dd, -(DATEPART(dw, DateOfSale)-1), DateOfSale),120),10)) + | |
| ',0)) As [Week beginning ' + | |
| Cast(DatePart(Month, DATEADD(dd, -(DATEPART(dw, DateOfSale)-1), DateOfSale)) as CHAR(2)) + | |
| ' / ' + | |
| Cast(DatePart(day, DATEADD(dd, -(DATEPART(dw, DateOfSale)-1), DateOfSale)) as CHAR(2)) + | |
| ']' | |
| From dbo.ProductInfo | |
| Group By DATEADD(dd, -(DATEPART(dw, DateOfSale)-1), DateOfSale) | |
| set @sql = N' | |
| Select ProductName, | |
| ' + @SelectCols + ' | |
| From | |
| ( | |
| Select ProductName, | |
| DATEPART(week, DateOfSale) as WeekNumber, | |
| DATEPART(Year, DateOfSale) as YearNumber, | |
| DATEADD(dd, -(DATEPART(dw, DateOfSale)-1), DateOfSale) as [StartOfWeek], | |
| SUM(SaleValue) as [TotalSales] | |
| From dbo.ProductInfo | |
| Group By ProductName, | |
| DATEPART(week, DateOfSale) , | |
| DATEPART(Year, DateOfSale) , | |
| DATEADD(dd, -(DATEPART(dw, DateOfSale)-1), DateOfSale) | |
| )q | |
| PIVOT ( | |
| Sum(TotalSales) for StartOfWeek in (' + @cols + ') | |
| )p | |
| Group By ProductName' | |
| Execute (@sql) | |
| Drop Table dbo.ProductInfo |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment