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 program a quarterly sales report with CTE and PIVOT?

Execute the following TSQL example script in SQL Server Management Studio Query Editor to demonstrate how to apply CTE and PIVOT to create crosstab summary reports.

-- SQL Server Common Table Expression - CTE

-- SQL Server PIVOT operator for creating crosstab report

-- SQL convert rows to columns - SQL Server coalesce and stuff functions

USE AdventureWorks;

WITH cteQuarterBigOrders

     AS (SELECT DatePart(qq,OrderDate) AS Quarter,

                p.Name,

                OrderAmount = convert(MONEY,LineTotal)

         FROM   Sales.SalesOrderHeader AS soh

                INNER JOIN Sales.SalesOrderDetail AS sod

                  ON soh.SalesOrderID = sod.SalesOrderID

                INNER JOIN Production.Product AS p

                  ON sod.ProductID = p.ProductID

         WHERE  DatePart(yy,OrderDate) = 2003  -- alternate YEAR(OrderDate)

                AND sod.LineTotal >= 5000)

SELECT   ProductName = Name,

-- SQL Server currency formatting - format money with thousand separators

         COALESCE('$' + stuff(convert(VARCHAR,[1],1),charindex('.',

convert(VARCHAR,[1],1)),3,''),'') AS 'Q1',

         COALESCE('$' + stuff(convert(VARCHAR,[2],1),charindex('.',

convert(VARCHAR,[2],1)),3,''),'') AS 'Q2',

         COALESCE('$' + stuff(convert(VARCHAR,[3],1),charindex('.',

convert(VARCHAR,[3],1)),3,''),'') AS 'Q3',

         COALESCE('$' + stuff(convert(VARCHAR,[4],1),charindex('.',

convert(VARCHAR,[4],1)),3,''),'') AS 'Q4'

FROM     cteQuarterBigOrders

         PIVOT

         (SUM(OrderAmount)

          FOR Quarter IN ( [1],[2],[3],[4] ) ) AS P

ORDER BY ProductName

GO

-- Results

ProductName Q1 Q1 Q3 Q4
HL Mountain Frame - Black, 42 $17,814 $12,146
HL Mountain Frame - Silver, 38 $5,209 $51,922
HL Mountain Frame - Silver, 42 $6,549
HL Mountain Frame - Silver, 46 $5,954
HL Road Frame - Black, 44 $5,153 $5,153
HL Road Frame - Red, 44 $6,012
HL Road Frame - Red, 48 $10,306
HL Road Frame - Red, 62 $6,012 $5,153
HL Touring Frame - Blue, 50 $5,421
HL Touring Frame - Blue, 54 $35,207 $6,023
HL Touring Frame - Blue, 60 $20,886 $6,276
HL Touring Frame - Yellow, 54 $39,943 $6,847
HL Touring Frame - Yellow, 60 $19,147
Mountain-200 Black, 38 $113,110 $177,042 $354,340 $278,238
Mountain-200 Black, 42 $111,168 $122,945 $258,090 $285,037
Mountain-200 Black, 46 $89,750 $72,538 $162,485 $154,223
Mountain-200 Silver, 38 $118,070 $132,985 $262,499 $235,685
Mountain-200 Silver, 42 $92,493 $96,942 $209,384 $136,415
Mountain-200 Silver, 46 $100,671 $84,971 $213,560 $151,727
Mountain-300 Black, 38 $12,584 $10,367
Mountain-300 Black, 40 $13,812 $17,768
Mountain-300 Black, 44 $5,831 $11,015
Mountain-300 Black, 48 $24,828 $28,511
Mountain-400-W Silver, 40 $5,248
Road-250 Black, 44 $96,861 $160,999 $183,251 $121,678
Road-250 Black, 48 $91,625 $113,877 $141,353 $115,814
Road-250 Black, 52 $65,446 $106,023 $95,290 $61,572
Road-250 Black, 58 $70,682 $82,463 $52,776 $58,640
Road-250 Red, 44 $120,212 $183,251
Road-250 Red, 48 $107,018 $126,076
Road-250 Red, 52 $68,902 $120,212
Road-250 Red, 58 $64,137 $60,211 $48,378 $57,174
Road-350-W Yellow, 40 $229,672 $181,039
Road-350-W Yellow, 42 $100,511 $108,182
Road-350-W Yellow, 44 $24,494 $40,823
Road-350-W Yellow, 48 $256,823 $212,604
Road-550-W Yellow, 38 $5,402 $36,515 $16,807
Road-550-W Yellow, 40 $25,794
Road-550-W Yellow, 48 $12,826 $26,785 $12,384
Road-650 Black, 44 $5,340
Road-650 Black, 52 $6,136 $22,501
Road-650 Black, 58 $16,466
Road-650 Red, 44 $18,117 $37,740
Road-650 Red, 48 $6,230 $12,295
Road-650 Red, 60 $6,230 $17,802
Road-650 Red, 62 $21,807
Road-750 Black, 48 $5,925
Touring-1000 Blue, 46 $222,298 $172,495
Touring-1000 Blue, 50 $101,561 $97,270
Touring-1000 Blue, 54 $50,065 $21,456
Touring-1000 Blue, 60 $292,281 $198,392
Touring-1000 Yellow, 46 $89,259 $153,772
Touring-1000 Yellow, 50 $11,443 $101,561
Touring-1000 Yellow, 54 $5,340 $31,469
Touring-1000 Yellow, 60 $123,590 $210,123
Touring-2000 Blue, 46 $13,427
Touring-2000 Blue, 54 $96,741 $63,941
Touring-2000 Blue, 60 $52,289 $32,378
Touring-3000 Blue, 50 $6,981
Touring-3000 Yellow, 62 $5,063

Related article:

http://msdn.microsoft.com/en-us/library/ms177410.aspx

 

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