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 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to apply PIVOT and dynamic SQL for purchase summary?

Execute the following SQL Server T-SQL script in SSMS Query Editor to demonstrate the use of dynamic SQL and PIVOT operator to create a summary report.

USE AdventureWorks2008;

 

DECLARE @SQL NVARCHAR(max), @PivotYears VARCHAR(MAX)

DECLARE @DDYear TABLE ([Year] int)

 

-- Get years for pivoting dynamically

INSERT @DDYear ([Year])

 SELECT * FROM (SELECT DISTINCT DDYear=YEAR(DueDate)

 FROM Purchasing.PurchaseOrderDetail ) x

 ORDER BY DDYear

 

-- Form list for PIVOT statement

SELECT @PivotYears = COALESCE(@PivotYears +

                     ',[', '[') + CONVERT(CHAR(4),[Year]) + ']'

FROM @DDYear

 

-- Assemble query

SET @SQL = 'SELECT ProductName,

[2001]=COALESCE([2001], 0),

[2002]=COALESCE([2002], 0),

[2003]=COALESCE([2003], 0),

[2004]=COALESCE([2004], 0)

 FROM (SELECT  ProductName=p.Name,  YEAR(DueDate) OrderYear,

 OrderQty

  FROM Purchasing.PurchaseOrderDetail pod

  JOIN Production.Product p

  ON pod.ProductID = p.ProductID) x

 PIVOT (SUM(OrderQty) FOR OrderYear IN (' + @PivotYears + ')) pvt

 ORDER BY ProductName'

 

 

PRINT @SQL  -- debug print to Messages window

EXEC sp_executeSQL @SQL

GO

 

Partial results:

ProductName 2001 2002 2003 2004
HL Road Rim 550 3300 11550 34650
HL Road Seat/Saddle 0 2750 6600 24200
HL Road Tire 0 2200 9350 27500
HL Shell 0 2200 6050 19800
HL Spindle/Axle 0 4400 12650 39050
HL Touring Seat/Saddle 0 1650 4400 15400

 

 

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