Using PIVOT relational operator in SQL Server 2005
Problem:
We have a sample table ABC:
id |
date |
name | attendance |
1 | 1/1/2009 | a |
1 |
2 |
1/1/2009 |
b |
0 |
3 |
2/1/2009 |
a |
0 |
4 |
2/1/2009 |
b |
1 |
we need the result like:
Name | 1/1/2009 | 2/1/2009 |
a |
Present | Absent |
b |
Absent | Present |
Solution:
We can use the PIVOT relational operator to change a table-valued expression into another table.
select * from
(select
date ,[name],
case
when attendance =0 then 'Absent'
else 'Present' end attendance
from ABC)p PIVOT
(
max( attendance)
FOR date IN
( [1/1/2009], [2/1/2009], [3/1/2009],[4/1/2009],[5/1/2009],[6/1/2009] )
) AS pvt