From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Cc: | pgsql(at)toddb(dot)mailshell(dot)com |
Subject: | Re: Formatting results |
Date: | 2005-09-22 22:08:41 |
Message-ID: | 200509221508.42021.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sigrid Meneses | 2005-09-23 04:47:59 | Re: QUERY A PSQL DB USING CRONTAB |
Previous Message | Andrej Ricnik-Bay | 2005-09-22 19:40:23 | Re: Import dbf data |