How to have conditional columns in a select statement with MS SQL Server
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
How to do a IF-THEN-ELSE in the SQL select statement
- SELECT empno, SUM(emptime) AS "Hours Worked", CASE
- WHEN SUM(emptime) < 4 THEN 'Beach bum'
- WHEN SUM(emptime) = 4 THEN 'OK'
- WHEN SUM(emptime) > 4 THEN 'Hard worker'
- END AS "Evaluation"
- FROM emp_work
- GROUP BY empno
- ORDER BY 3;
Data used
- SELECT empno, emptime
- FROM emp_work ;
empno emptime ------ --------------------------------------- 000010 0.50 000010 1.00 000010 0.50 000020 1.00 000030 0.50 000030 0.50 000050 0.25 000050 0.75 000070 1.00 000090 1.00 000100 1.00 000110 1.00 000130 1.00 000130 0.50 000140 0.50 000140 1.00 000140 0.50 000140 0.50 000140 0.50 000150 1.00 000150 1.00 000160 1.00 000170 1.00 000170 1.00 000170 1.00 000180 1.00 000190 1.00 000190 1.00 000200 1.00 000200 1.00 000210 0.50 000210 0.50 000220 1.00 000230 1.00 000230 0.50 000230 0.50 000230 0.50 000230 1.00 000240 1.00 000240 1.00 000250 1.00 000250 0.50 000250 0.50 000250 1.00 000250 0.50 000250 1.00 000250 0.25 000250 0.25 000250 0.50 000250 0.50 000260 0.50 000260 1.00 000260 1.00 000260 0.50 000260 0.50 000260 1.00 000260 0.50 000270 0.50 000270 1.00 000270 0.25 000270 0.75 000270 1.00 000270 1.00 000270 0.50 000280 1.00 000290 1.00 000300 1.00 000310 1.00 000320 0.75 000320 0.25 000330 0.25 000330 0.75 000340 0.50 000340 0.50 000020 1.00 (75 row(s) affected)
Case Statement
The case statement:
- Only applies to one column.
- You can have more than one case statement per select.
- You can have a case statement based on another previous case statement.
Evaluate the employee based on the billable hours:
- SELECT empno, SUM(emptime) AS "Hours Worked",
- CASE
- WHEN SUM(emptime) < 4 THEN 'Beach bum'
- WHEN SUM(emptime) = 4 THEN 'OK'
- WHEN SUM(emptime) > 4 THEN 'Hard worker'
- END AS 'Evaluation'
- FROM emp_work
- GROUP BY empno;
empno Hours Worked Evaluation ------ --------------------------------------- ----------- 000010 2.00 Beach bum 000020 2.00 Beach bum 000030 1.00 Beach bum 000050 1.00 Beach bum 000070 1.00 Beach bum 000090 1.00 Beach bum 000100 1.00 Beach bum 000110 1.00 Beach bum 000130 1.50 Beach bum 000140 3.00 Beach bum 000150 2.00 Beach bum 000160 1.00 Beach bum 000170 3.00 Beach bum 000180 1.00 Beach bum 000190 2.00 Beach bum 000200 2.00 Beach bum 000210 1.00 Beach bum 000220 1.00 Beach bum 000230 3.50 Beach bum 000240 2.00 Beach bum 000250 6.00 Hard worker 000260 5.00 Hard worker 000270 5.00 Hard worker 000280 1.00 Beach bum 000290 1.00 Beach bum 000300 1.00 Beach bum 000310 1.00 Beach bum 000320 1.00 Beach bum 000330 1.00 Beach bum 000340 1.00 Beach bum (30 row(s) affected)
Sorting on case
There are 2 ways of sorting on the case statement:
- The column number
- Explicit column with an inline view
Sorting by column number
- SELECT empno, SUM(emptime) AS "Hours Worked", CASE
- WHEN SUM(emptime) < 4 THEN 'Beach bum'
- WHEN SUM(emptime) = 4 THEN 'OK'
- WHEN SUM(emptime) > 4 THEN 'Hard worker'
- END AS "Evaluation"
- FROM emp_work
- GROUP BY empno
- ORDER BY 3;
empno Hours Worked Evaluation ------ --------------------------------------- ----------- 000010 2.00 Beach bum 000020 2.00 Beach bum 000030 1.00 Beach bum 000050 1.00 Beach bum 000070 1.00 Beach bum 000090 1.00 Beach bum 000100 1.00 Beach bum 000110 1.00 Beach bum 000130 1.50 Beach bum 000140 3.00 Beach bum 000150 2.00 Beach bum 000160 1.00 Beach bum 000170 3.00 Beach bum 000180 1.00 Beach bum 000190 2.00 Beach bum 000200 2.00 Beach bum 000210 1.00 Beach bum 000220 1.00 Beach bum 000230 3.50 Beach bum 000240 2.00 Beach bum 000280 1.00 Beach bum 000290 1.00 Beach bum 000300 1.00 Beach bum 000310 1.00 Beach bum 000320 1.00 Beach bum 000330 1.00 Beach bum 000340 1.00 Beach bum 000250 6.00 Hard worker 000260 5.00 Hard worker 000270 5.00 Hard worker (30 row(s) affected)
Sorting by the column number is:
- Simpler to write.
- More difficult to maintain later in the life of the select statement, you will need to ensure that the case statement always stays the third column.
- Could be faster than an inline view. But MS SQL Server will optimize the inline view and rewrite it with sorting by column number.
Sorting by column name
To sort by the column name, you will need to use an inline view see: alias in where clause for detailed explanations of inline views.
- SELECT * FROM (
- SELECT empno, SUM(emptime) AS "Hours Worked", CASE
- WHEN SUM(emptime) < 4 THEN 'Beach bum'
- WHEN SUM(emptime) = 4 THEN 'OK'
- WHEN SUM(emptime) > 4 THEN 'Hard worker'
- END AS "Evaluation"
- FROM emp_work
- GROUP BY empno) AS ABC
- ORDER BY "Hours Worked";
empno Hours Worked Evaluation ------ --------------------------------------- ----------- 000030 1.00 Beach bum 000050 1.00 Beach bum 000070 1.00 Beach bum 000090 1.00 Beach bum 000100 1.00 Beach bum 000110 1.00 Beach bum 000160 1.00 Beach bum 000180 1.00 Beach bum 000210 1.00 Beach bum 000220 1.00 Beach bum 000280 1.00 Beach bum 000290 1.00 Beach bum 000300 1.00 Beach bum 000310 1.00 Beach bum 000320 1.00 Beach bum 000330 1.00 Beach bum 000340 1.00 Beach bum 000130 1.50 Beach bum 000190 2.00 Beach bum 000200 2.00 Beach bum 000010 2.00 Beach bum 000020 2.00 Beach bum 000240 2.00 Beach bum 000150 2.00 Beach bum 000170 3.00 Beach bum 000140 3.00 Beach bum 000230 3.50 Beach bum 000260 5.00 Hard worker 000270 5.00 Hard worker 000250 6.00 Hard worker (30 row(s) affected)
Sorting by the column name is:
- More complicated to understand.
- Less prone for problem later in the life of the select statement.
- Usually as fast as sorting by column number. But MS SQL Server will optimize the inline view and rewrite it with sorting by column number.
