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

CASE expression usage in SQL Server T-SQL

Nested CASE expression example:

-- CASE expressions can be nested upto 10 levels

SELECT ProductName=Name, Price=FORMAT(ListPrice,'c2','en-US'), ProductColor=

  CASE WHEN Color is NULL THEN 'N/A'

      ELSE CASE

                      WHEN CHARINDEX('/', Color) = 0 THEN Color

                                  ELSE CONCAT('Multi: ',Color) END END

FROM Production.Product  ORDER BY ProductColor;

/* ProductName  Price       ProductColor

....

HL Road Pedal      $80.99   Multi: Silver/Black

Touring Pedal       $80.99   Multi: Silver/Black

Mountain Tire Tube             $4.99      N/A ......*/

 

CASE expression usage examples:

  1. ProductLine single column data pivoted to columns
  2. Instant lookup table replacement
  3. CASE in GROUP BY
  4. PIVOT months for crosstab(matrix) report
  5. Crosstab query by year (rows) and by quarter (columns)
  6. Transpose rows into columns
  7. Complex/Special Sorting/ORDER BY

 

-- FIRST EXAMPLE - layout vertical ProductLine across columns

USE AdventureWorks;

SELECT   TOP 10

             ProductNumber,

         Road = CASE ProductLine

                  WHEN 'R' THEN 'X'

                  ELSE ''

                END,

         Mountain = CASE ProductLine

                      WHEN 'M' THEN 'X'

                      ELSE ''

                    END,

         Touring = CASE ProductLine

                     WHEN 'T' THEN 'X'

                     ELSE ''

                   END,

         OtherItem = CASE ProductLine

                       WHEN 'R' THEN ''

                       WHEN 'M' THEN ''

                       WHEN 'T' THEN ''

                       ELSE 'X'

                     END,

         ProductName = Name

FROM     Production.Product

ORDER BY NEWID();

GO

/* Results

 

ProductNumber Road Mountain Touring OtherItem ProductName
BK-M82S-38 X Mountain-100 Silver, 38
BK-T18Y-44 X Touring-3000 Yellow, 44
BK-M68S-46 X Mountain-200 Silver, 46
MS-2341 X Metal Sheet 5
BK-T79U-54 X Touring-1000 Blue, 54
RW-R762 X ML Road Rear Wheel
FR-M94B-42 X HL Mountain Frame - Black, 42
HN-1224 X Hex Nut 7
SH-W890-M X Women's Mountain Shorts, M
HB-M243 X LL Mountain Handlebars

*/

 

-- SECOND EXAMPLE - instant category lookup table implementation

USE AdventureWorks;

 

SELECT   TOP 300

    ProductName = Name,

    ListPrice,

    Color = coalesce(Color,'N/A'),

    'Price Category' = CASE

                         WHEN ListPrice < 25.00 THEN 'Bargain'

                         WHEN ListPrice < 100.00 THEN 'Inexpensive'

                         WHEN ListPrice >= 100.00

                              AND ListPrice <= 1000.00 THEN 'Average'

                         WHEN ListPrice < 3000.00 THEN 'Expensive'

                         ELSE 'Very Expensive'

                       END

FROM     Production.Product

WHERE    ListPrice > 0

ORDER BY ListPrice DESC,

         ProductName

GO

/* Partial results

 

ProductName ListPrice Color Price Category
HL Touring Frame - Blue, 46 1003.91 Blue Expensive
HL Touring Frame - Blue, 50 1003.91 Blue Expensive
HL Touring Frame - Blue, 54 1003.91 Blue Expensive
HL Touring Frame - Blue, 60 1003.91 Blue Expensive
HL Touring Frame - Yellow, 46 1003.91 Yellow Expensive
HL Touring Frame - Yellow, 50 1003.91 Yellow Expensive
HL Touring Frame - Yellow, 54 1003.91 Yellow Expensive
HL Touring Frame - Yellow, 60 1003.91 Yellow Expensive
Road-650 Black, 44 782.99 Black Average
Road-650 Black, 48 782.99 Black Average
Road-650 Black, 52 782.99 Black Average
Road-650 Black, 58 782.99 Black Average
Road-650 Black, 60 782.99 Black Average

*/

 

-- THIRD EXAMPLE - CASE with GROUP BY

use AdventureWorks2008;

select PriceRange =

  case

      when SubTotal between 0 and 500 then 'Tiny Order'

      when SubTotal between 500.0001 and 1500 then 'Small Order'

      when SubTotal between 1500.0001 and 10000 then 'Average Order'

      when SubTotal between 10000.0001 and 50000 then 'Large Order'

      else 'Big Ticket Order' end,

  TotalDollar = SUM (SubTotal),

  TotalOrders = COUNT(*)

from Sales.SalesOrderHeader

group by

  case

      when SubTotal between 0 and 500 then 'Tiny Order'

      when SubTotal between 500.0001 and 1500 then 'Small Order'

      when SubTotal between 1500.0001 and 10000 then 'Average Order'

      when SubTotal between 10000.0001 and 50000 then 'Large Order'

      else 'Big Ticket Order' end

order by TotalDollar desc

go

/* Results

 

PriceRange TotalDollar TotalOrders
Big Ticket Order 55875712.17 703
Large Order 36628337.49 1202
Average Order 29122600.13 10677
Small Order 4987315.572 5997
Tiny Order 723214.7526 12886

*/

 

-- FOURTH EXAMPLE - PIVOT report by month

USE AdventureWorks2008;

SELECT [Year]=YEAR(OrderDate),

       SUM(CASE WHEN MONTH(OrderDate) = 1 THEN SubTotal END) AS 'JAN'

      ,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN SubTotal END) AS 'FEB'

      ,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN SubTotal END) AS 'MAR'

      ,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN SubTotal END) AS 'APR'

      ,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN SubTotal END) AS 'MAY'

      ,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN SubTotal END) AS 'JUN'

      ,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN SubTotal END) AS 'JUL'

      ,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN SubTotal END) AS 'AUG'

      ,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN SubTotal END) AS 'SEP'

      ,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN SubTotal END) AS 'OCT'

      ,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN SubTotal END) AS 'NOV'

      ,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN SubTotal END) AS 'DEC'

FROM Sales.SalesOrderHeader

GROUP BY YEAR(OrderDate)

ORDER BY [Year]

GO

/* Partial results

 

Year JAN FEB MAR APR MAY JUN
2002 1453197 2833324 2391929 1724737 3401203 2304183
2003 2021335 3353516 2363458 2752819 4027046 2947980
2004 3340283 4712382 4771753 4274109 5899389 6088719

 

*/

 

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

-- FIFTH EXAMPLE - Crosstab query by year & by month

-- T-SQL pivot using the case function - Crosstab query

-- SQL group by aggregate - T-SQL currency conversion

SELECT YEAR = YEAR(OrderDate),

       COALESCE('$'+CONVERT(VARCHAR,SUM(CASE

                                      WHEN DATEPART(QQ,OrderDate) = 1 THEN Subtotal

                                    END),1),'') AS 'Q1',

       COALESCE('$'+CONVERT(VARCHAR,SUM(CASE

                                      WHEN DATEPART(QQ,OrderDate) = 2 THEN Subtotal

                                    END),1),'') AS 'Q2',

       COALESCE('$'+CONVERT(VARCHAR,SUM(CASE

                                      WHEN DATEPART(QQ,OrderDate) = 3 THEN Subtotal

                                    END),1),'') AS 'Q3',

       COALESCE('$'+CONVERT(VARCHAR,SUM(CASE

                                      WHEN DATEPART(QQ,OrderDate) = 4 THEN Subtotal

                                    END),1),'') AS 'Q4'

FROM     AdventureWorks2008.Sales.SalesOrderHeader

GROUP BY YEAR(OrderDate)

ORDER BY YEAR(OrderDate)

 

/*

YEAR  Q1                Q2                Q3                Q4

2001                                      $5,294,961.92     $7,671,148.64

2002  $6,678,449.12     $7,430,122.29     $12,179,372.04    $9,798,486.39

2003  $7,738,309.35     $9,727,845.55     $16,488,806.73    $15,192,201.07

2004  $12,824,418.47    $16,262,217.91    $50,840.63 

*/

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

-- SIXTH EXAMPLE - Transpose rows into columns

 

-- Applying the CASE function for transposing rows into columns

-- Create a vertical test table to be used in the transpose with SELECT INTO

USE tempdb;

SELECT CustomerID,

       Label = cast('Name' AS VARCHAR(32)),

       VALUE = cast(ContactName AS VARCHAR(64))

INTO   NameAddress

FROM   Northwind.dbo.Customers

UNION ALL

SELECT CustomerID,

       Label = cast('Address' AS VARCHAR(32)),

       Address

FROM   Northwind.dbo.Customers

UNION ALL

SELECT CustomerID,

       Label = cast('CityStateZip' AS VARCHAR(32)),

       City + ', ' + isnull(Region,'') + ' ' + PostalCode

FROM   Northwind.dbo.Customers

GO

 

SELECT   *

FROM     NameAddress

ORDER BY CustomerID,

         Label

 

/* Partial results

CustomerID  Label             Value

LEHMS       Address           Magazinweg 7

LEHMS       CityStateZip      Frankfurt a.M.,  60528

LEHMS       Name              Renate Messner

LETSS       Address           87 Polk St. Suite 5

LETSS       CityStateZip      San Francisco, CA 94117

LETSS       Name              Jaime Yorres

*/

 

-- Transpose rows into columns - SELECT from SELECT

SELECT   CustomerID,

         Name = MIN(Name),

         [Address] = MIN(Address),

         CityStateZip = MIN(CityStateZip)

FROM     (SELECT CustomerID,

                 Name = CASE Label

                          WHEN 'Name' THEN VALUE

                        END,

                 [Address] = CASE Label

                               WHEN 'Address' THEN VALUE

                             END,

                 CityStateZip = CASE Label

                                  WHEN 'CityStateZip' THEN VALUE

                                END

          FROM   NameAddress) x

GROUP BY CustomerID

ORDER BY CustomerID

 

/* Partial results

CustomerID  Name              Address                       CityStateZip

ALFKI      Maria Anders      Obere Str. 57                 Berlin,  12209

ANATR      Ana Trujillo      Avda. de la Constitución 2222 México D.F.,  05021

ANTON      Antonio Moreno    Mataderos  2312               México D.F.,  05023

AROUT      Thomas Hardy      120 Hanover Sq.               London,  WA1 1DP

*/

 

DROP TABLE NameAddress

GO

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

-- SEVENTH EXAMPLE - Complex sorting / ORDER BY

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

-- T-SQL complex sorting (ORDER BY) using the CASE function

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

-- Major sort on Color if not null, else on product name

-- Minor sort on ProductNumber - SQL Case in Order By

USE AdventureWorks2008;

SELECT      ProductID,

            ProductNumber,

            ProductName = Name,

            Color,

            ListPrice

FROM Production.Product

WHERE Name LIKE ('%Touring%')

ORDER BY    CASE

                  WHEN Color IS NULL THEN Name

                  ELSE Color

            END,

            ProductNumber DESC

/* ProductID ProductNumber ProductName          Color ListPrice

....

      978   BK-T18U-44  Touring-3000 Blue, 44   Blue 742.35

      842   PA-T100     Touring-Panniers, Large Grey 125.00

      947   HB-T928     HL Touring Handlebars   NULL 91.57

      522   SA-T872     HL Touring Seat Assembly NULL 196.92

      916   SE-T924     HL Touring Seat/Saddle  NULL 52.64 ....*/

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

Related article:

Dynamic CASE PIVOT

The Best SQL Server Training in the World