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
http://weblogs.asp.net/stef/archive/2009/06/07/using-pivot-relational-operator-in-sql-server-2005.aspx