MSSQL - Pivot Sample

So you have some result that you like to pivot the data in SQL Server.  Here is how:

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