Execute the following Microsoft SQL Server T-SQL sample script in SSMS Query Editor to demonstrate the design of a dynamic PIVOT for crosstab (matrix) report generation:

-- MSSQL pivot crosstab report - SQL dynamic pivot - SQL dynamic sql

-- SQL dynamic crosstab report with pivot

USE AdventureWorks

GO

DECLARE  @OrderYear  AS  TABLE(

                               YYYY INT    NOT NULL    PRIMARY KEY

                               )

DECLARE  @DynamicSQL  AS NVARCHAR(4000)

INSERT INTO @OrderYear

SELECT DISTINCT YEAR(OrderDate)

FROM   Sales.SalesOrderHeader

DECLARE  @ReportColumnNames  AS NVARCHAR(MAX),

         @IterationYear      AS INT

SET @IterationYear = (SELECT MIN(YYYY)

                      FROM   @OrderYear)

SET @ReportColumnNames = N''

-- Assemble pivot list dynamically

WHILE (@IterationYear IS NOT NULL)

  BEGIN

    SET @ReportColumnNames = @ReportColumnNames + N',' +
      QUOTENAME(CAST(@IterationYear AS NVARCHAR(10)))

    SET @IterationYear = (SELECT MIN(YYYY)

                          FROM   @OrderYear

                          WHERE  YYYY > @IterationYear)

  END

SET @ReportColumnNames = SUBSTRING(@ReportColumnNames,2,LEN(@ReportColumnNames))

PRINT @ReportColumnNames

-- [2001],[2002],[2003],[2004]

SET @DynamicSQL = N'SELECT * FROM (SELECT [Store (Freight Summary)]=s.Name, 

  YEAR(OrderDate) AS OrderYear,  Freight = convert(money,convert(varchar, Freight)) 

  FROM Sales.SalesOrderHeader soh  JOIN Sales.Store s 

  ON soh.CustomerID = s.CustomerID) as Header 

  PIVOT (SUM(Freight)

  FOR OrderYear IN(' + @ReportColumnNames + N'))  AS Pvt ORDER BY 1'

PRINT @DynamicSQL -- Testing & debugging

/*

SELECT * FROM (SELECT [Store (Freight Summary)]=s.Name, 

YEAR(OrderDate) AS OrderYear,  Freight = convert(money,convert(varchar, Freight)) 

FROM Sales.SalesOrderHeader soh  JOIN Sales.Store s 

ON soh.CustomerID = s.CustomerID) as Header 

PIVOT (SUM(Freight) FOR OrderYear IN([2001],[2002],[2003],[2004])) 

AS Pvt ORDER BY 1

*/

-- Execute dynamic sql

EXEC sp_executesql @DynamicSQL

GO

/* Partial results

Store (Freight Summary)             2001        2002        2003        2004

Grease and Oil Products Company     104.02      555.02      726.75      272.28

Great Bicycle Supply                4430.26     3871.35     NULL        NULL

Great Bikes                         1653.89     7445.16     7525.98     4584.63

Greater Bike Store                  489.79      1454.78     864.08      245.22

*/