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@envisity.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@agliodbs.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@agliodbs.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@godal.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@postgresql.org)


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