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 d开发者_如何学Cate, 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).

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

The exact syntax will depend on your database engine, but in PostgreSQL, and probably others, this will work. This may work verbatim in Oracle, as Postgres tends to copy Oracle syntax a lot.

SELECT
    date,
    SUM((Status='Processing')::INT) AS Processing,
    SUM((Status='Active')::INT) AS Active,
    SUM((Status='Closed')::INT) AS Closed
FROM accounts
GROUP BY date;

you can use build in pivot function in oracle as well...

SELECT * FROM accounts 
pivot ( count(distinct name) for status in ('Active','Processing','Closed')); 

this function is suggested by person having good experience in oracle database.. that's why i pasted here :-)

This is the standard way (before Oracle 11g) to do a pivot:

SQL> create table mytable (mydate,name,status)
  2  as
  3  select date '2011-07-01', 'Alice', 'Processing' from dual union all
  4  select date '2011-07-01', 'Alice', 'Active' from dual union all
  5  select date '2011-07-02', 'Alice', 'Closed' from dual union all
  6  select date '2011-07-01', 'Bob', 'Active' from dual union all
  7  select date '2011-07-02', 'Charlie', 'Processing' from dual
  8  /

Table created.

SQL> select mydate
  2       , count(case status when 'Active' then 1 end)     a
  3       , count(case status when 'Processing' then 1 end) p
  4       , count(case status when 'Closed' then 1 end)     c
  5    from mytable
  6   group by mydate
  7   order by mydate
  8  /

MYDATE                       A          P          C
------------------- ---------- ---------- ----------
01-07-2011 00:00:00          2          1          0
02-07-2011 00:00:00          0          1          1

2 rows selected.

Regards,
Rob.