DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to architect dynamic PIVOT?

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

*/

Related link: http://sqlusa.com/bestpractices/dynamicsql/

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE