Re: Is it possible in PostgreSQL? - Mailing list pgsql-sql

This is what I use to flatten a table, the syntax may not be postgresql 
correct but you will get idea.

SELECTa.name,SUM (CASE         WHEN EXTRACT(month from a.date) = 1 THEN a.quantity        ELSE 0        END)  AS
'01',SUM(CASE       WHEN EXTRACT(month from a.date) = 2 THEN a.quantity        ELSE 0        END) AS '02'etc,etc.
 
FROM(SELECT DISTINCT name    FROM "whatever") as aJOIN"whatever" as b on a.name = b.name
WHERE-- put in year range
GROUP BY a.name

The "a" table could b a temp table with the know values to speed up execution.  
The main thing is to only scan the table once.

Let me know how it works out for you.


On Sunday 18 January 2004 07:02, you wrote:
> Moving thread over to SQL list as it belongs there.
>
> Bronx:  This certainly is possible, but IMO, not in one query.  Actually
> doing it will be relatively complex.  For purposes of maintenance, I am
> thinking that doing this would be better handled by wrapping at least one
> view.
>
> CREATE VIEW sales_pre_proc AS
> SELECT name, quantity, to_char("date", 'YYYY') AS year, to_char("date",
> 'MM') FROM sales;
>
> This is needed for the group by statement below to function properly:
> CREATE VIEW sales_month_summary AS
> SELECT name, sum(quantity) AS quantity, year, month from sales_pre_proc
> GROUP BY name, year, month;
>
> This will give you a view that will have the sum information.  Now we just
> have to create the statement which will create the pivot effect.  I
> understand that there is something under contrib/tablefunc for this, but I
> do not have it on my system (cygwin), at the moment.  Perhaps someone else
> can help.
>
> Failing that, you can write your own function to return each row.  I was
> working on a quick proof of concept but it was not working properly.
>
> Best Wishes,
> Chris Travers
>
>   ----- Original Message -----
>   From: Bronx
>   To: pgsql-admin@postgresql.org
>   Sent: Tuesday, January 13, 2004 6:58 AM
>   Subject: [ADMIN] Is it possible in PostgreSQL?
>
>
>   Hi,
>   I've got problem with one specific query. I've got the table
>   with many of rekords like these:
>
>   name     |  quantity    | date
>   -------------------------------------------------------
>   aaa            2                2003-04-01
>   bbb            4                2003-04-12
>   ccc            5                2003-05-12
>   aaa            3                2003-01-14
>   aaa            1                2003-12-09
>   bbb            9                2003-08-08
>
>   and so on ...
>
>   Does anybody know how make query which return grouped
>   records by month of year and name (also sum of quantity).
>   It is possible to make a query whitch return something like that:
>
>   name | 01 | 02 | 03 | 04 | ... | 12 (months)
>   ------------------------------------------------
>   aaa     x     x     x    x    ...   x
>   bbb     x     x     x    x    ...   x
>   ccc     x     x     x    x    ...   x
>
>   where x means sum of quantity in month.
>   It is possible to make it in one query?
>   I know that in Access is construction : PIVOT.
>
>   Thanks
>   Adam