Re: transposing data for a view

How about:

SELECT
scanid,
sum ( CASE WHEN region = 'A' THEN volume ELSE NULL
)
AS A_volume,
sum ( CASE WHEN region = 'B' THEN volume ELSE NULL
)
AS B_volume,
sum ( CASE WHEN region = 'C' THEN volume ELSE NULL
)
AS C_volume
FROM table
GROUP BY scanid;

Requires that you know in advance the range of region
values.

A bit shorter than some of the other approaches? :-)

BTW, I don't believe the self-join approach proposed
earlier will work, because joining on "scanid" will
create a cartesian type join where the region values
will be duplicated (multiplicated!).

--- Aasmund Midttun Godal <postgresql(at)envisity(dot)com>
wrote:
> I think this might do the trick...
>
> CREATE FUNCTION hori_view() RETURNS BOOLEAN AS '
> DECLARE
> view_select TEXT;
> view_from TEXT;
> view_where TEXT;
> column_name TEXT;
> last_column_name TEXT;
> g_row generalized_table%ROWTYPE;
> BEGIN
> SELECT region INTO column_name
> FROM generalized_table ORDER BY region LIMIT 1;
> view_select := ''SELECT '' || column_name ||
> ''.scanid, '' || column_name || ''.volume AS "''
> ||
> column_name || ''_volume"'';
> view_from := '' FROM generalized_table '' ||
> column_name;
> view_where := '' WHERE '' ||column_name ||
> ''.region = '''''' || column_name || '''''''';
> last_column_name := column_name;
> FOR g_row IN SELECT DISTINCT ON (region) *
> FROM generalized_table ORDER BY region OFFSET 1
> LOOP
> view_select := view_select || '', '' ||
> g_row.region ||
> ''.volume AS "'' || g_row.region || ''_volume"'';
> view_from := view_from || '' JOIN
> generalized_table '' ||
> g_row.region || '' ON ('' || last_column_name ||
> ''.scanid = '' || g_row.region || ''.scanid)'';
> view_where := view_where || '' AND '' ||
> g_row.region ||
> ''.region = '''''' || g_row.region || '''''''';
> last_column_name := g_row.region;
> END LOOP;
> EXECUTE ''CREATE VIEW generalized_view AS '' ||
> view_select ||
> view_from || view_where;
> RETURN TRUE;
> END;
> ' LANGUAGE 'plpgsql';
>
> SELECT hori_view();
>
> SELECT * FROM generalized_view;
>
> Ok, it may not be pretty but it works,
>
> Regards,
>
> Aasmund.
>
> On Wed, 31 Oct 2001 12:42:10 -0800, "Josh Berkus"
> <josh(at)agliodbs(dot)com> wrote:
> > Jeremy,
> >
> > First, to do a pivot table, you have to be using
> Postgres 7.1.x. 7.0.x
> > will not do it. So upgrade now.
> >
> >
> > There are two approaches, the simple approach and
> the complex. The
> > simple approach requires you to know in advance of
> building the view all
> > of the possible values for your category column.
> The complex approach,
> > which is dynamic, requires a rather sophisticated
> function (which I will
> > write eventually, really!) so we won't go into it
> here.
> >
> > The simple approach is to create each column as a
> sub-select in the FROM
> > clause of your statement. So, per the example
> above:
> >
> > SELECT scanid, A_volume, B_volume, C_volume
> > FROM (SELECT scanid FROM volumes GROUP BY scanid)
> scan
> > LEFT OUTER JOIN
> > (SELECT scanid, volume as A_volume FROM volumes
> WHERE region = 'A') av
> > ON scan.scanid = av.scanid LEFT OUTER JOIN
> > (SELECT scanid, volume as B_volume FROM volumes
> WHERE region = 'B') bv
> > ON scan.scanid = bv.scanid LEFT OUTER JOIN
> > (SELECT scanid, volume as C_volume FROM volumes
> WHERE region = 'C') cv
> > ON scan.scanid = cv.scanid
> > ORDER BY scanid;
> >
> > This approach can be adapted to include aggregates
> and the like.
> >
> > -Josh Berkus
> >
> > ______AGLIO DATABASE
> SOLUTIONS___________________________
> > Josh Berkus
> > Complete information technology
> josh(at)agliodbs(dot)com
> > and data management solutions (415)
> 565-7293
> > for law firms, small businesses fax
> 621-2533
> > and non-profit organizations. San
> Francisco
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> Aasmund Midttun Godal
>
> aasmund(at)godal(dot)com - http://www.godal.com/
> +47 40 45 20 46
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
majordomo(at)postgresql(dot)org)

__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com