SET QUOTED_IDENTIFIER
OFF
GO
SET NOCOUNT
ON
DECLARE @SQL
VARCHAR(MAX), @Cols VARCHAR(MAX)=NULL, @Cols2 VARCHAR(MAX)
DECLARE @RowCount
INT = 0, @Upper INT = 90, @Lower INT = 0, @Random INT
-- Let's create a temp table with random data
CREATE TABLE
TestTable (OrderDate DATETIME, QTY INT)
WHILE @RowCount
< 3000
BEGIN
SET @Random = ROUND(((@Upper - @Lower - 1.0) * RAND() + @Lower), 0)
INSERT INTO TestTable SELECT DATEADD(dd, @Random, DATEADD(dd, 1-DATEPART(dd, GETDATE()), GETDATE())),ROUND(((@Upper - @Lower - 1.0) * RAND() + @Lower), 0)
SET @RowCount = @RowCount + 1
END
-- Build all needed column names in a single string
SELECT @Cols
= COALESCE(@Cols + ',[' + CAST(DATEPART(yy, OrderDate) AS VARCHAR(10)) + ']', '[' + CAST(DATEPART(yy, OrderDate) AS VARCHAR(10)) + ']')
FROM TestTable
WHERE OrderDate
>= '01/01/'+CAST(DATEPART(yy, DATEADD(yy, -1, GETDATE())) AS VARCHAR(8))
GROUP BY DATEPART(yy, OrderDate)
ORDER BY DATEPART(yy, OrderDate)
SELECT @Cols
-- This is the real sql to select data with pivot form
SELECT @SQL
= "
SELECT [Month] [QTY], " + @Cols + " FROM (
SELECT [Year], [Month], [MonthNum], CASE WHEN [QTY] = 0 THEN NULL
ELSE [QTY] END [QTY] FROM (
SELECT DATEPART(yy, OrderDate) [Year], DATENAME(mm, OrderDate)
[Month], DATEPART(mm, OrderDate) [MonthNum], SUM(QTY) [QTY]
FROM TestTable
WHERE OrderDate >= '01/01/'+CAST(DATEPART(yy, DATEADD(yy, -1,
GETDATE())) AS VARCHAR(8))
GROUP BY DATEPART(yy, OrderDate), DATENAME(mm, OrderDate),
DATEPART(mm, OrderDate)) t) a
PIVOT(SUM(QTY) FOR [Year] IN (" + @Cols + ")) b
ORDER BY MonthNum"
EXEC (@SQL)
DROP TABLE
TestTable
