title: Get grouped data from a table as columns instead of rows
body: <p>I have a table with the following schema: (Date, Name, Status) where State can be one of 'Processing', 'Active' and 'Closed'. Sample entries are:</p>
  
  <pre><code>|------------+---------+------------|
  |       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 |
  |------------+---------+------------|
  </code></pre>
  
  <p>I am using the following query to get a count of number of accounts in different statuses:</p>
  
  <pre><code>select date, status, count(distinct name)
  from accounts
  group by date, status
  </code></pre>
  
  <p>And it gives the following:</p>
  
  <pre><code>2011-07-01 Processing 1
  2011-07-01 Active 2
  2011-07-02 Active 1
  2011-07-02 Closed 1
  </code></pre>
  
  <p>How can I get the data in columns? i.e.,</p>
  
  <pre><code>2011-07-01 2 1 0
  2011-07-02 1 0 1
  </code></pre>
  
  <p>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).</p>