sql server 2008 - Three column SQL PIVOT

Question

How do I do a sql pivot of data that looks like this, USING the SQL PIVOT command ?

id           |    field     |   value
---------------------------------------
1            |    year      |   2011
1            |    month     |   August
2            |    year      |   2009
1            |    day       |   21
2            |    day       |   31
2            |    month     |   July
3            |    year      |   2010
3            |    month     |   January
3            |    day       |   NULL

Into something that looks like this:

id  |  year  |  month  |  day
-----------------------------
1     2011     August    21
2     2010      July     31
3     2009     January   NULL

Answer 1

;WITH DATA(id,field,value) AS
(
SELECT 1,'year','2011' UNION ALL
SELECT 1,'month','August' UNION ALL
SELECT 2,'year','2009' UNION ALL
SELECT 1,'day ','21' UNION ALL
SELECT 2,'day ','31' UNION ALL
SELECT 2,'month','July' UNION ALL
SELECT 3,'year','2010' UNION ALL
SELECT 3,'month','January' UNION ALL
SELECT 3,'day ',NULL
)
SELECT id,
       year,
       month,
       day
FROM   DATA PIVOT (MAX(value) FOR field IN ([year], [month], [day])) AS Pvt