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 use CASE function to form conditional expressions?

Execute the following Microsoft SQL Server T-SQL database scripts in Management Studio Query Editor to demonstrate the use of the CASE expression to build complex conditional expressions.

-- SQL CASE function in WHERE clause - QUICK SYNTAX

DECLARE @ProductID INT = NULL  -- input parameter

SELECT * FROM AdventureWorks2008.Production.Product

WHERE ProductID = CASE

                    WHEN @ProductID is not null THEN @ProductID

                    ELSE ProductID END

GO

-- (504 row(s) affected)

DECLARE @ProductID INT = 800  -- input parameter

SELECT * FROM AdventureWorks2008.Production.Product

WHERE ProductID = CASE

                    WHEN @ProductID is not null THEN @ProductID

                    ELSE ProductID END

-- (1 row(s) affected)

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

-- SQL CASE function simple conditional expression syntax
SELECT ProductName=Name,
       ListPrice,
       Color = CASE WHEN Color is NULL THEN 'N/A'
                    WHEN Color = 'Multi' THEN 'Mixed'
                    ELSE Color END
FROM AdventureWorks2008.Production.Product
ORDER BY Color

-- SQL CASE function to avoid divide by zero
SELECT ProductName=Name,
       StandardCost,
       ListPrice,
       CostToPrice = StandardCost /
                     CASE WHEN ListPrice > 0.0 THEN ListPrice ELSE 1.0 END
FROM AdventureWorks2008.Production.Product
------------

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

-- CASE function in WHERE clause predicate for complex filtering - CASE function ORDER BY

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

USE AdventureWorks

GO

CREATE PROCEDURE sprocGetContactInfo

                @SortField CHAR(10),

                @AscOrDesc CHAR(4),

                @Title     CHAR(5)  = NULL

AS

BEGIN

  SET nocount  ON

  SELECT   ContactID,

           Title,

           FirstName,

           LastName,

           Phone

  FROM     Person.Contact

  WHERE    Title = CASE

                     WHEN @Title IS NOT NULL THEN @Title

                     ELSE Title

                   END

  ORDER BY CASE @AscOrDesc    -- Complex sorting with CASE

             WHEN 'asc' THEN CASE @SortField

                               WHEN 'FirstName' THEN FirstName

                               WHEN 'LastName' THEN LastName

                             END

           END ASC,

           CASE @AscOrDesc

             WHEN 'desc' THEN CASE @SortField

                                WHEN 'FirstName' THEN FirstName

                                WHEN 'LastName' THEN LastName

                              END

           END DESC

END

GO

 

EXEC sprocGetContactInfo   'LastName',   'desc',   'Mr.'

/* ContactID      Title FirstName   LastName    Phone

      994         Mr.   Michael     Zwilling    542-555-0100

      988         Mr.   Arvid       Ziegler     398-555-0100

      986         Mr.   Frank       Zhang       889-555-0100 .... */

 

EXEC sprocGetContactInfo   'FirstName',   'asc',   'Ms.'

 

 

EXEC sprocGetContactInfo   'LastName',   'asc',   'Sr.'
------------

-- SQL CASE function to transpose rows to columns
USE
AdventureWorks2008;

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

*/

 

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

-- T-SQL CASE function - create range descriptions by list price - translate
------------

USE AdventureWorks2008;

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

*/

-- SQL CASE function in 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

*/

 

-- SQL CASE to create PIVOT query / report
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

 

*/

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

------------
-- T-SQL PIVOT query 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 

*/

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

-- 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

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

Related article:

CASE (Transact-SQL)

Using the CASE expression instead of dynamic SQL in SQL Server

 

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