T-SQl Select Case Example

T-SQL: Select Case Example

Select Case allows you to present data in various way. You can take numbers and return text values and express
certian conditions based on the value of a certain column.

Another example would be that you have a table Naughty_Nice in the Santa Database a data column Nice (INT Datatype) .
There are two distinct values in the column. The Value of 0 equals 'No' and the value 1 equals yes. You wanted to speed
stuff up and use less space by selecting a number and not use text Naughty/Nice.

Santa needs a list and he complains that 1 and 0 will not make sense to him at 30,000 feet. It's so hard to
communicate to a guy that still thinks wooden toys are a big hit. He's a bit too hands-on, and lacks the technical
skills to really understand that we want to optimize the database.

You want to query the table to find out which Child is Naughty and which is nice, but return a value that 'Ole Red' will
benefit from. So you quickly point out that a New Yankee Workshop Maraton is on and Norm is demonstrating some $200,000
combination tablesaw, sander with an auto-jig thingy. He takes off and you write the following query.

Select 	c.ChildName, 
	g.Gift, 
	l.Location,  
	case(n.Nice)
	when 1 then 'Nice' 
	Else 
	'Naughty'
	 End
From
Child c, Gift G, Location L, Naughty_Nice n
Where 
c.childid=g.childid
and 
c.childid=n.childid
Order by 
N.Nice N.nice, l.location,g.Gift, c.ChildName

You followed Normalization Rules that would join the tables Child, Gift, Location and Naughty_Nice by Childid.
You are Sooooo Good!

Below is an example that is a bit more complex, it takes a peek at the Orders table in the MS Sql Server NorthWind
Database. Drop off in June 1998? Hummh? I guess Pubs sold out to a major online bookseller or was pushed out of
business by slow sales.

---This example uses the SQL Server Example Database NorthWind
---Purpose: Define by Month and Year Total Orders for Years 1996-1998
---Use Case Function to gather Totals by Year
---Example By Jack Donnell [email protected] 

USE NorthWind
GO

USE NorthWind
GO

SELECT
	
  MONTH(OrderDate) AS Month,
  SUM(CASE YEAR(OrderDate)
        WHEN 1996 THEN 1
        ELSE 0
      END) AS Orders1996,
  SUM(CASE YEAR(OrderDate)
        WHEN 1997 THEN 1
        ELSE 0
      END) AS Orders1997,
  SUM(CASE YEAR(OrderDate)
        WHEN 1998 THEN 1
        ELSE 0
      END) AS Orders1998
FROM Orders
GROUP BY MONTH(OrderDate)
ORDER BY MONTH(OrderDate)
COMPUTE SUM(SUM(CASE YEAR(OrderDate)
        WHEN 1996 THEN 1
        ELSE 0
      END) ),
SUM(SUM(CASE YEAR(OrderDate)
        WHEN 1997 THEN 1
        ELSE 0
      END)),
sum(SUM(CASE YEAR(OrderDate)
        WHEN 1998 THEN 1
        ELSE 0
      END))


The OutPut Would be :

Month       Orders1996  Orders1997  Orders1998  
----------- ----------- ----------- ----------- 
1           0           33          55
2           0           29          54
3           0           30          73
4           0           31          74
5           0           32          14
6           0           30          0
7           22          33          0
8           25          33          0
9           23          37          0
10          26          38          0
11          25          34          0
12          31          48          0

            sum
            ===========
            152

                        sum
                        ===========
                        408

                                    sum
                                    ===========
                                    270


(13 row(s) affected)

Return to JackDonnell.com