Re: transposing data for a view
От | Jeff Eckermann |
---|---|
Тема | Re: transposing data for a view |
Дата | |
Msg-id | 20011101154744.98632.qmail@web20802.mail.yahoo.com обсуждение исходный текст |
Ответ на | Re: transposing data for a view ("Aasmund Midttun Godal" <postgresql@envisity.com>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: