Thread: Formatting results

Here is my table:

table tst:

id |    date             | num
---+---------------------+----
 1 | 2005-09-22 14:37:21 |  5
 2 | 2005-09-22 10:22:33 |  3
 3 | 2005-09-21 10:22:34 |  3
 4 | 2005-09-14 10:33:21 |  5
 5 | 2005-09-22 17:34:22 |  5

Here is my query:

SELECT num,date_trunc('day',date),COUNT(*) FROM tst
 WHERE date_trunc('day',date)<'2005-09-23' AND date_trunc('day',date)>'2005-09-13' GROUP BY num,date_trunc('day',date)

I realize that my date ranges use the entire table in this example.  In real life my table has data outside the
selectedrange. 

Here are my results:

num|    date_trunc       | count
---+---------------------+----
 3 | 2005-09-21 00:00:00 | 1
 3 | 2005-09-22 00:00:00 | 1
 5 | 2005-09-14 00:00:00 | 1
 5 | 2005-09-22 00:00:00 | 2

Which is what I would expect.  What I would like is the output formatted like this:

num| 2005-09-14 | 2005-09-21 | 2005-09-22
---+------------+------------+-----------
 3 |     0      |      1     |      1
 5 |     1      |      0     |      2


Is there a SQL way to do this?  I'd rather not use a stored procedure, and I would like to be able to do this on
databasesbesides postgres.  (I am current using postgres, so that is my primary focus.  I just like to be general if
possible)

Thanks.


Todd

Todd,

> Which is what I would expect.  What I would like is the output formatted
> like this:
>
> num| 2005-09-14 | 2005-09-21 | 2005-09-22
> ---+------------+------------+-----------
>  3 |     0      |      1     |      1  
>  5 |     1      |      0     |      2
>
>
> Is there a SQL way to do this?  I'd rather not use a stored procedure,
> and I would like to be able to do this on databases besides postgres.
>  (I am current using postgres, so that is my primary focus.  I just like
> to be general if possible)

Well, the easiest way to do it in PostgreSQL is to use crosstab()
from /contrib/tablefunc.

That's not, of course, portable.   For a review of ways to do it in
standard SQL, see Joe Celko's "SQL for Smarties, 2nd ed.", which has a
whole chapter on the topic.   In your case, though, that's going to
require generating some dynamic SQL on on the application layer, though.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco