need help with CASE function

[sdurant]

I have a table
Student Subject Score
Sally Math 98
Sally Reading 96
Billy Science 72
Billy Reading 63

I want a result
Student MathScore ReadingScore
Sally 98 96
Billy null 63

My SQL
Select student,
case when subject=Math then Score else null end as MathScore,
case when subject=Reading then Score else null end as ReadingScore

My result
Student MathScore ReadingScore
Sally 98 null
Sally null 96
Billy null null
Billy null 63

Help?

[ck9663]

Is there any error you're getting?

~~ CK

[sdurant]

No error, it just doesn't return the result I want. See example.

[ck9663]

Try to use PIVOT/UNPIVOT to accomplish your goal.

Happy Coding!!!

~~ CK

[sdurant]

Thanks for the tip, it's exactly what I need. Sadly, I'm not a sysadmin so I can't set the compatibility level to make the PIVOT function work. I guess it's just lip service when Microsoft says it wants to push access down to the "knowledge worker".

Anyway, thanks for your help!