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 Server Training Scripts

PIVOT & UNPIVOT usage in SQL Server T-SQL

The following PIVOT-UNPIVOT operator T-SQL scripts demonstrate the usage of the operators on a simple dataset to generate crosstab(matrix) result:

 -- EXAMPLE - PIVOT AW8 quarterly sales

USE AdventureWorks2008;

 

/****************** PIVOT OPERATION *******************/

WITH cteQuery

     AS (SELECT   YEAR = YEAR(orderDate),

                  QUARTER = DatePart(qq,OrderDate),

                  Sales = Sum(TotalDue)

         FROM     Sales.SalesOrderHeader

         GROUP BY YEAR(orderDate), DatePart(qq,OrderDate))

/* "Vertical" Sales data in many rows and 1 column from CTE

YEAR  QUARTER     Sales

2001  3     5850932.9483

2002  4     10827327.4904

2003  2     10749269.374

2002  1     7379686.3091

2002  2     8210285.1655

2004  1     14170982.5455

2003  3     18220131.5285

2003  1     8550831.8702

2004  2     17969750.9487

2001  4     8476619.278

2003  4     16787382.3141

2002  3     13458206.13

2004  3     56178.9223

*/        

SELECT   YEAR,  Q1 = [1],  Q2 = [2], Q3 = [3], Q4 = [4]

INTO     #PivotXtab

FROM     (SELECT * FROM cteQUERY) AS PivotInput

         PIVOT

         (SUM(Sales) -- Aggregate for cells

          FOR QUARTER IN ( [1],[2],[3],[4] ) ) AS PivotOutput

ORDER BY YEAR;

 

SELECT * FROM   #PivotXtab

GO

/*  Pivotted sales data

YEAR  Q1                Q2          Q3          Q4

2001  NULL              NULL        5850932.94  8476619.278

2002  7379686.3091      8210285.165 13458206.13 10827327.4904

2003  8550831.8702      10749269.37 18220131.52 16787382.3141

2004  14170982.5455     17969750.94 56178.9223  NULL

*/

 

/****************** UNPIVOT OPERATION *******************/

SELECT   YEAR,

         Quarter,

         Sales

FROM     (SELECT *

          FROM   #PivotXtab) p

         UNPIVOT

         (Sales

          FOR Quarter IN ( [Q1],[Q2],[Q3],[Q4] ) ) AS unpvt

ORDER BY YEAR,

         Quarter;

GO

/* Sales data in many rows again and in 1 column

Year  Quarter     Sales

2001  Q3    5850932.9483

2001  Q4    8476619.278

2002  Q1    7379686.3091

2002  Q2    8210285.1655

2002  Q3    13458206.13

2002  Q4    10827327.4904

2003  Q1    8550831.8702

2003  Q2    10749269.374

2003  Q3    18220131.5285

2003  Q4    16787382.3141

2004  Q1    14170982.5455

2004  Q2    17969750.9487

2004  Q3    56178.9223

*/

 

DROP TABLE #PivotXtab

GO

------------

Related article:

http://www.sqlusa.com/bestpractices2005/dynamicpivot/

 

The Best SQL Server Training in the World