Q:

Get grouped data from a table as columns instead of rows

I have a table with the following schema: (Date, Name, Status) where State can be one of 'Processing', 'Active' and 'Closed'. Sample entries are:
|------------+---------+------------|
|       Date | Name    | Status     |
|------------+---------+------------|
| 2011-07-01 | Alice   | Processing |
| 2011-07-01 | Alice   | Active     |
| 2011-07-02 | Alice   | Closed     |
| 2011-07-01 | Bob     | Active     |
| 2011-07-02 | Charlie | Processing |
|------------+---------+------------|

I am using the following query to get a count of number of accounts in different statuses:
select date, status, count(distinct name)
from accounts
group by date, status

And it gives the following:
2011-07-01 Processing 1
2011-07-01 Active 2
2011-07-02 Active 1
2011-07-02 Closed 1

How can I get the data in columns? i.e.,
2011-07-01 2 1 0
2011-07-02 1 0 1

Where the first column is date, second column is number of accounts in Active status, third column is number of accounts in Processing status and the last column is number of accounts in Closed status? The only I can think of is writing three sub-queries for each of the statuses and doing a parent query to select each of them. But it doesn't seem very extensible (e.g., if I have to add a new status, I have modify the parent query and add one more sub-query).

A:

SELECT date
   , SUM(CASE status WHEN 'Active'     THEN cnt ELSE 0 END) AS a
   , SUM(CASE status WHEN 'Processing' THEN cnt ELSE 0 END) AS p
   , SUM(CASE status WHEN 'Closed'     THEN cnt ELSE 0 END) AS c
FROM 
  ( SELECT date, status, COUNT(DISTINCT name) AS cnt
    FROM accounts
    GROUP BY date, status
  ) grp
GROUP BY date
ORDER BY date

