Received: from ns.krot.org (ns.krot.org [193.212.103.165]) by postgresql.org (8.11.3/8.11.4) with SMTP id fA13RtP96994 for ; Wed, 31 Oct 2001 22:27:55 -0500 (EST) (envelope-from postgresql@envisity.com) Received: (qmail 29408 invoked by uid 67); 1 Nov 2001 03:26:07 -0000 Message-ID: <20011101032607.28876.qmail@ns.krot.org> Content-Type: text/plain; charset=ISO-8859-1 Content-Disposition: inline MIME-Version: 1.0 From: "Aasmund Midttun Godal" To: josh@agliodbs.com Cc: jeremy@wundt.psychiatry.uiowa.edu, pgsql-sql@postgresql.org Subject: Re: transposing data for a view Date: Thu, 01 Nov 2001 03:26:07 GMT X-Mailer: Googley-Moogley In-Reply-To: from Josh Berkus on Wed, 31 Oct 2001 12:42:10 -0800 References: Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id fA13W4M97652 X-Archive-Number: 200110/435 X-Sequence-Number: 5386 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" 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