Can this be done with a single query?

Colin,
Pivot operations is exactly what I needed. I applied the pivot type code aga
inst my real
SQL 2000 table and it is doing what I was looking for.
Thanks you very much.
Gaetan
On Sun, 26 Mar 2006 13:09:12 GMT, "Colin Dawson" <newsgroups@.cjdawson.com> w
rote:

>This is a simple pivot operation.
>In SQL 2000 it'll work like this.
>Select
>s.student
>From (
>Select
>student,
>max( case when subject = 'math' then grade end ) math,
>max( case when subject = 'physics' then grade end ) physics,
>max( case when subject = 'chemistry' then grade end ) chemistry
>From #student
>Group by student
> ) s
>where s.math >= 85
>and s.physics >= 85
>and s.chemistry >= 85
>
>and in SQL 2005 it works like this.
>Select
>student
>From #student s
>Pivot ( Max( s.grade ) For s.subject in ( [math], [physics], [chemistry] ) )
>as pvt
>where pvt.math >= 85
>and pvt.physics >= 85
>and pvt.chemistry >= 85
>
>The execution plan for the SQL2005 example is slightly more efficient, but
>only be removing one compute scaler from the plan. All in all, that's not
>an issue.
>
>Regards
>Colin Dawson
>www.cjdawson.com
>
>"Gaetan" <me@.somewhere.com> wrote in message
> news:m9sb221pmojvn8t9m368gnopdshmjti023@.
4ax.com...
>thx.
Colin.