재우니의 블로그

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