SQL query to pivot data

Question

I have this sort of table

+------------------+
| Name Date   Category|
+------------------+
| Test1 1/1/2001   2 |
| Test2 2/1/2001   2 |
| Test3 3/1/2001   2 |
| Foo1  5/4/2011   2 |
| Foo2  6/4/2011   2 |
| Test1  6/4/2011  3 |
+------------------+

And I would like to show the results like this :

Category   Test1Date    Foo2Date
-------------------------------
2         1/1/2011      6/4/2001    
3         6/4/2011      NULL

How would I do this in SQL?

Answer 1

You can convert your rows of data into columns using an aggregate function with a CASE expression:

select category,
  max(case when name = 'Test1' then date end) Test1Date,
  max(case when name = 'Foo2' then date end) Foo2Date
from yourtable
group by category;

See SQL Fiddle with Demo