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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to prepare a crosstab report using CASE?

Execute the following Microsoft SQL Server T-SQL script to demonstrate the usage of the CASE conditional function to create a Territory by OrderYear crosstab report on TotalDue amount:

USE AdventureWorks

 

SELECT   Territory = LEFT(t.Name,20),

         '$' + convert(VARCHAR,SUM(CASE

                                     WHEN YEAR(OrderDate) = 2001 THEN TotalDue

                                     ELSE 0

                                   END),1) AS YY2001,

         '$' + convert(VARCHAR,SUM(CASE

                                     WHEN YEAR(OrderDate) = 2002 THEN TotalDue

                                     ELSE 0

                                   END),1) AS YY2002,

         '$' + convert(VARCHAR,SUM(CASE

                                     WHEN YEAR(OrderDate) = 2003 THEN TotalDue

                                     ELSE 0

                                   END),1) AS YY2003,

         '$' + convert(VARCHAR,SUM(CASE

                                     WHEN YEAR(OrderDate) = 2004 THEN TotalDue

                                     ELSE 0

                                   END),1) AS YY2004

FROM     Sales.SalesOrderHeader soh

         JOIN Sales.SalesTerritory t

           ON soh.TerritoryID = t.TerritoryID

GROUP BY LEFT(t.Name,20)

ORDER BY Territory

/*

Territory   YY2001            YY2002            YY2003                  YY2004

Australia   $1,446,497.17     $2,380,484.84     $4,313,294.84     $3,674,099.25

Canada      $1,866,734.92     $6,130,230.74     $6,964,947.20     $3,437,016.33

Central     $1,071,338.24     $2,959,946.93     $3,389,909.30     $1,492,104.78

France      $199,531.72       $1,535,232.90     $3,815,005.25     $2,569,979.48

Germany     $262,752.42       $575,960.10       $2,432,549.83     $2,208,557.23

Northeast   $640,271.75       $2,755,131.43     $3,232,129.25     $1,192,677.20

Northwest   $2,361,885.58     $4,855,977.10     $6,447,526.64     $4,396,271.05

Southeast   $1,632,139.25     $3,179,192.09     $2,742,200.43     $1,330,567.59

Southwest   $2,889,892.04     $8,489,320.98     $9,960,675.06     $5,810,706.50

UK          $322,207.53       $1,602,371.32     $3,873,251.75     $2,776,218.11

*/

 

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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