datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
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