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