datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
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