SqlServer Series Notes-- case when statement

CASE usage

CASE

WHEN condition1 THEN returnvalue1

WHEN condition 2 THEN returnvalue2

WHEN condition 3 THEN returnvalue3

ELSE defaultreturnvalue

END

Equivalent to if... Else... Else... .

SELECT FName, FWeight

(CASE

WHEN FWeight50 THEN 'Feifei'

ELSE 'ok'

END)

As isnormal

FROM T_Person

Create table bs

(

Bsname char (20)

Bsflag char (20)

Bstime datetime

)

Insert bs values ('Rocket', 'win', '2008-01-10')

Insert bs values ('Rocket', 'negative', '2008-01-11')

Insert bs values ('Rocket', 'win', '2008-01-10')

Insert bs values ('Bull', 'negative', '2008-01-10')

Insert bs values ('Bull', 'Sheng', '2008-01-11')

Insert bs values ('Bull', 'negative', '2008-01-10')

Select * from bs

Select bsname, win = sum (case when bsflag= 'win' then 1 else 0 end)

Negative = sum (case when bsflag=' negative 'then 1 else 0 end)

From bs group by bsname