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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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
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