pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
transposing data for a view
13+ messages / 6 participants
[nested] [flat]

* transposing data for a view
@ 2001-10-31 00:36 H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
  2001-10-31 07:51 ` Re: transposing data for a view Arian Prins <prinsarian@zonnet.nl>
  2001-10-31 19:59 ` Re: transposing data for a view James Orr <james@lrgmail.com>
  2001-10-31 20:42 ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
  0 siblings, 3 replies; 13+ messages in thread

From: jeremy@wundt.psychiatry.uiowa.edu @ 2001-10-31 00:36 UTC (permalink / raw)
  To: pgsql-sql

I have a generalized table:

scanid | region | volume
-------------------------
1          A      34.4
1          B      32.1
1          C      29.1
2          A      32.4
2          B      33.2
2          C      35.6
.
.
.

I want to create a flattened out view that looks like the following:

scanid | A_volume | B_volume | C_volume
----------------------------------------
1        34.4         32.1      29.1
2        32.4         33.2      35.6
.
.
.

How do I correctly/efficiently construct a psql query to
pivot/transpose the data?  I am using postgreSQL version 7.0.x

thanks,
Jeremy



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: transposing data for a view
  2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
@ 2001-10-31 07:51 ` Arian Prins <prinsarian@zonnet.nl>
  2001-10-31 18:27   ` Re: transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
  2 siblings, 1 reply; 13+ messages in thread

From: Arian Prins @ 2001-10-31 07:51 UTC (permalink / raw)
  To: H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>



H Jeremy Bockholt schreef:

> I have a generalized table:
>
> scanid | region | volume
> -------------------------
> 1          A      34.4
> 1          B      32.1
> 1          C      29.1
> 2          A      32.4
> 2          B      33.2
> 2          C      35.6
> .
> .
> .
>
> I want to create a flattened out view that looks like the following:
>
> scanid | A_volume | B_volume | C_volume
> ----------------------------------------
> 1        34.4         32.1      29.1
> 2        32.4         33.2      35.6
> .
> .
> .
>
> How do I correctly/efficiently construct a psql query to
> pivot/transpose the data?  I am using postgreSQL version 7.0.x
>
> thanks,
> Jeremy

Try This:

select region, sum(a_volume) AS a_volume,
                     sum(b_volume) AS b_volume,
                    sum(c_volume) AS c_volume
from (
    select
        scanid,
        volume AS a_volume,
        0 AS b_volume,
        0 AS c_volume
    from mytable
    where region = A
    UNION
    select
        scanid,
        0 AS a_volume,
        volume AS b_volume,
        0 AS c_volume
    from mytable
    where region = B
    UNION
    select
        scanid,
       0 AS a_volume,
        0 AS b_volume,
        volume AS c_volume
    from mytable
    where region = C
 ) tmp

(you might have to specifically typecast the zero's)

It would probably also be possible using CASE-statements. This is just
_one_ idea.

Arian.




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: transposing data for a view
  2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
  2001-10-31 07:51 ` Re: transposing data for a view Arian Prins <prinsarian@zonnet.nl>
@ 2001-10-31 18:27   ` H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
  2001-11-01 16:07     ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 13+ messages in thread

From: jeremy@wundt.psychiatry.uiowa.edu @ 2001-10-31 18:27 UTC (permalink / raw)
  To: pgsql-sql

Hi,
I see how your idea could work--the only thing I would change would
be region needs to be scanid on the first line; however,
I'm using PostgreSQL v7.03, so I can not do subqueries within FROM clause.

Other than creating a temporary table, is there a way to
restructure this solution to get around this limitation?

thanks,
jeremy

Arian Prins <prinsarian@zonnet.nl> wrote in message news:<3BDFAD88.6BB058DA@zonnet.nl>...
> H Jeremy Bockholt schreef:
> 
> > I have a generalized table:
> >
> > scanid | region | volume
> > -------------------------
> > 1          A      34.4
> > 1          B      32.1
> > 1          C      29.1
> > 2          A      32.4
> > 2          B      33.2
> > 2          C      35.6
> > .
> > .
> > .
> >
> > I want to create a flattened out view that looks like the following:
> >
> > scanid | A_volume | B_volume | C_volume
> > ----------------------------------------
> > 1        34.4         32.1      29.1
> > 2        32.4         33.2      35.6
> > .
> > .
> > .
> >
> > How do I correctly/efficiently construct a psql query to
> > pivot/transpose the data?  I am using postgreSQL version 7.0.x
> >
> > thanks,
> > Jeremy
> 
> Try This:
> 
> select region, sum(a_volume) AS a_volume,
>                      sum(b_volume) AS b_volume,
>                     sum(c_volume) AS c_volume
> from (
>     select
>         scanid,
>         volume AS a_volume,
>         0 AS b_volume,
>         0 AS c_volume
>     from mytable
>     where region = A
>     UNION
>     select
>         scanid,
>         0 AS a_volume,
>         volume AS b_volume,
>         0 AS c_volume
>     from mytable
>     where region = B
>     UNION
>     select
>         scanid,
>        0 AS a_volume,
>         0 AS b_volume,
>         volume AS c_volume
>     from mytable
>     where region = C
>  ) tmp
> 
> (you might have to specifically typecast the zero's)
> 
> It would probably also be possible using CASE-statements. This is just
> _one_ idea.
> 
> Arian.



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: transposing data for a view
  2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
  2001-10-31 07:51 ` Re: transposing data for a view Arian Prins <prinsarian@zonnet.nl>
  2001-10-31 18:27   ` Re: transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
@ 2001-11-01 16:07     ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Josh Berkus @ 2001-11-01 16:07 UTC (permalink / raw)
  To: jeremy@wundt.psychiatry.uiowa.edu; pgsql-sql

Jeremy,

> I see how your idea could work--the only thing I would change would
> be region needs to be scanid on the first line; however,
> I'm using PostgreSQL v7.03, so I can not do subqueries within FROM
> clause.
> 
> Other than creating a temporary table, is there a way to
> restructure this solution to get around this limitation?

No, you need to upgrade.  What's the obstacle to using 7.1.3, anyway?

-Josh

______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







^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: transposing data for a view
  2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
@ 2001-10-31 19:59 ` James Orr <james@lrgmail.com>
  2 siblings, 0 replies; 13+ messages in thread

From: James Orr @ 2001-10-31 19:59 UTC (permalink / raw)
  To: H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>; pgsql-sql


----- Original Message ----- 
From: "H Jeremy Bockholt" <jeremy@wundt.psychiatry.uiowa.edu>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, October 30, 2001 7:36 PM
Subject: [SQL] transposing data for a view


> I have a generalized table:
> 
> scanid | region | volume
> -------------------------
> 1          A      34.4
> 1          B      32.1
> 1          C      29.1
> 2          A      32.4
> 2          B      33.2
> 2          C      35.6
> .
> .
> .
> 
> I want to create a flattened out view that looks like the following:
> 
> scanid | A_volume | B_volume | C_volume
> ----------------------------------------
> 1        34.4         32.1      29.1
> 2        32.4         33.2      35.6
> .
> .
> .
> 
> How do I correctly/efficiently construct a psql query to
> pivot/transpose the data?  I am using postgreSQL version 7.0.x

SELECT 
    A.scanid,
    A.volume AS A_volume,
    B.volume AS B_volume,
    C.volume AS C_volume
FROM
    table A JOIN
    table B ON (A.scanid = B.scanid) JOIN
    table C ON (B.scanid = C.scanid)
WHERE
    A.region = 'A' AND
    B.region = 'B' AND
    C.region = 'C'

- James




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: transposing data for a view
  2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
@ 2001-10-31 20:42 ` Josh Berkus <josh@agliodbs.com>
  2001-11-01 03:26   ` Re: transposing data for a view Aasmund Midttun Godal <postgresql@envisity.com>
  2 siblings, 1 reply; 13+ messages in thread

From: Josh Berkus @ 2001-10-31 20:42 UTC (permalink / raw)
  To: jeremy@wundt.psychiatry.uiowa.edu; pgsql-sql

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.

> I want to create a flattened out view that looks like the following:
> 
> scanid | A_volume | B_volume | C_volume
> ----------------------------------------
> 1        34.4         32.1      29.1
> 2        32.4         33.2      35.6

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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: transposing data for a view
  2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
  2001-10-31 20:42 ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
@ 2001-11-01 03:26   ` Aasmund Midttun Godal <postgresql@envisity.com>
  2001-11-01 15:47     ` Re: transposing data for a view Jeff Eckermann <jeff_eckermann@yahoo.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Aasmund Midttun Godal @ 2001-11-01 03:26 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: jeremy@wundt.psychiatry.uiowa.edu; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: transposing data for a view
  2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
  2001-10-31 20:42 ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
  2001-11-01 03:26   ` Re: transposing data for a view Aasmund Midttun Godal <postgresql@envisity.com>
@ 2001-11-01 15:47     ` Jeff Eckermann <jeff_eckermann@yahoo.com>
  2001-11-01 16:17       ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Jeff Eckermann @ 2001-11-01 15:47 UTC (permalink / raw)
  To: Aasmund Midttun Godal <postgresql@envisity.com>; josh@agliodbs.com; +Cc: jeremy@wundt.psychiatry.uiowa.edu; 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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: transposing data for a view
  2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
  2001-10-31 20:42 ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
  2001-11-01 03:26   ` Re: transposing data for a view Aasmund Midttun Godal <postgresql@envisity.com>
  2001-11-01 15:47     ` Re: transposing data for a view Jeff Eckermann <jeff_eckermann@yahoo.com>
@ 2001-11-01 16:17       ` Josh Berkus <josh@agliodbs.com>
  2001-11-01 16:22         ` Re: transposing data for a view Jeff Eckermann <jeff_eckermann@yahoo.com>
  2001-11-01 16:54         ` Re: transposing data for a view James Orr <james@lrgmail.com>
  0 siblings, 2 replies; 13+ messages in thread

From: Josh Berkus @ 2001-11-01 16:17 UTC (permalink / raw)
  To: Jeff Eckermann <jeff_eckermann@yahoo.com>; Aasmund Midttun Godal <postgresql@envisity.com>; josh@agliodbs.com; +Cc: jeremy@wundt.psychiatry.uiowa.edu; pgsql-sql

Jeff,

> 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;

Hey, that's an elegant solution to doing it in 7.0.3.  I hadn't thought
of it.  Jeremy, never mind what I said about being forced to upgrade.
Upgrading *would* still be a good idea, of course. 

Of couse, it's only *half* a solution.  Your query will result in:

scanid	A_volume  B_volume  C_volume
1	34.5				
1		  55.1		
1				12.3
2	11.1				
etc.

For the second half of the solution, Jeremy needs to create the above as
a view ('volume_rollup_1') and apply this second view:

SELECT scanid, SUM(A_volume) as A_volume, SUM(B_Volume) as B_Volume,
SUM(C_volume) as C_volume
FROM volume_rollup_1;

This will give Jeremy the "pivot" grid he's looking for.

> 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!).

Not if you're talking about my query, they won't.  I use that query form
in many projects to create roll-ups; it's the "best" SQL92 approach to
the "pivot table" problem.  However, it will not work in 7.0.3.

-Josh


______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







^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: transposing data for a view
  2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
  2001-10-31 20:42 ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
  2001-11-01 03:26   ` Re: transposing data for a view Aasmund Midttun Godal <postgresql@envisity.com>
  2001-11-01 15:47     ` Re: transposing data for a view Jeff Eckermann <jeff_eckermann@yahoo.com>
  2001-11-01 16:17       ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
@ 2001-11-01 16:22         ` Jeff Eckermann <jeff_eckermann@yahoo.com>
  2001-11-01 16:27           ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Jeff Eckermann @ 2001-11-01 16:22 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; Aasmund Midttun Godal <postgresql@envisity.com>; +Cc: jeremy@wundt.psychiatry.uiowa.edu; pgsql-sql

Josh,
I don't see how you got the result you quote below. 
My query sums the volume figures, grouping by scanid:
that worked as expected (one line per scanid) on my
system when I tested it (version 7.1.2, though I don't
think that matters).
Jeff

--- Josh Berkus <josh@agliodbs.com> wrote:

> Of couse, it's only *half* a solution.  Your query
> will result in:
> 
> scanid	A_volume  B_volume  C_volume
> 1	34.5				
> 1		  55.1		
> 1				12.3
> 2	11.1				
> etc.
> 



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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: transposing data for a view
  2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
  2001-10-31 20:42 ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
  2001-11-01 03:26   ` Re: transposing data for a view Aasmund Midttun Godal <postgresql@envisity.com>
  2001-11-01 15:47     ` Re: transposing data for a view Jeff Eckermann <jeff_eckermann@yahoo.com>
  2001-11-01 16:17       ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
  2001-11-01 16:22         ` Re: transposing data for a view Jeff Eckermann <jeff_eckermann@yahoo.com>
@ 2001-11-01 16:27           ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Josh Berkus @ 2001-11-01 16:27 UTC (permalink / raw)
  To: Jeff Eckermann <jeff_eckermann@yahoo.com>; Josh Berkus <josh@agliodbs.com>; Aasmund Midttun Godal <postgresql@envisity.com>; +Cc: jeremy@wundt.psychiatry.uiowa.edu; pgsql-sql

Jeff,

> I don't see how you got the result you quote below. 
> My query sums the volume figures, grouping by scanid:
> that worked as expected (one line per scanid) on my
> system when I tested it (version 7.1.2, though I don't
> think that matters).

Lemme try it ....

Yes, you're right.  Sorry!  Disregard my commentary about the second
view.

-Josh


______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







^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: transposing data for a view
  2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
  2001-10-31 20:42 ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
  2001-11-01 03:26   ` Re: transposing data for a view Aasmund Midttun Godal <postgresql@envisity.com>
  2001-11-01 15:47     ` Re: transposing data for a view Jeff Eckermann <jeff_eckermann@yahoo.com>
  2001-11-01 16:17       ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
@ 2001-11-01 16:54         ` James Orr <james@lrgmail.com>
  2001-11-01 17:44           ` Re: transposing data for a view Jeff Eckermann <jeff_eckermann@yahoo.com>
  1 sibling, 1 reply; 13+ messages in thread

From: James Orr @ 2001-11-01 16:54 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; Jeff Eckermann <jeff_eckermann@yahoo.com>; Aasmund Midttun Godal <postgresql@envisity.com>; +Cc: jeremy@wundt.psychiatry.uiowa.edu; pgsql-sql


> > 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!).
>
> Not if you're talking about my query, they won't.  I use that query form
> in many projects to create roll-ups; it's the "best" SQL92 approach to
> the "pivot table" problem.  However, it will not work in 7.0.3.

I think he might be talking about mine.  The region values will not be
duplicated, the WHERE clause prevents it.
I kind of prefer my own query aesthetically, is it as efficient internally?

- James




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: transposing data for a view
  2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
  2001-10-31 20:42 ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
  2001-11-01 03:26   ` Re: transposing data for a view Aasmund Midttun Godal <postgresql@envisity.com>
  2001-11-01 15:47     ` Re: transposing data for a view Jeff Eckermann <jeff_eckermann@yahoo.com>
  2001-11-01 16:17       ` Re: transposing data for a view Josh Berkus <josh@agliodbs.com>
  2001-11-01 16:54         ` Re: transposing data for a view James Orr <james@lrgmail.com>
@ 2001-11-01 17:44           ` Jeff Eckermann <jeff_eckermann@yahoo.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Jeff Eckermann @ 2001-11-01 17:44 UTC (permalink / raw)
  To: James Orr <james@lrgmail.com>; Josh Berkus <josh@agliodbs.com>; Aasmund Midttun Godal <postgresql@envisity.com>; +Cc: jeremy@wundt.psychiatry.uiowa.edu; pgsql-sql


--- James Orr <james@lrgmail.com> wrote:

> I think he might be talking about mine.  The region
> values will not be
> duplicated, the WHERE clause prevents it.

If you are saying that I didn't read the original
query closely enough, you're probably right. 
Unfortunately I deleted the original message, so I
can't check that.

> I kind of prefer my own query aesthetically, is it
> as efficient internally?

Someone more knowledgable will have to answer that:
though I would guess that working with three joined
tables would slow things down somewhat.
> 
> - James
> 


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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread


end of thread, other threads:[~2001-11-01 17:44 UTC | newest]

Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-31 00:36 transposing data for a view H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
2001-10-31 07:51 ` Arian Prins <prinsarian@zonnet.nl>
2001-10-31 18:27   ` H Jeremy Bockholt <jeremy@wundt.psychiatry.uiowa.edu>
2001-11-01 16:07     ` Josh Berkus <josh@agliodbs.com>
2001-10-31 19:59 ` James Orr <james@lrgmail.com>
2001-10-31 20:42 ` Josh Berkus <josh@agliodbs.com>
2001-11-01 03:26   ` Aasmund Midttun Godal <postgresql@envisity.com>
2001-11-01 15:47     ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2001-11-01 16:17       ` Josh Berkus <josh@agliodbs.com>
2001-11-01 16:22         ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2001-11-01 16:27           ` Josh Berkus <josh@agliodbs.com>
2001-11-01 16:54         ` James Orr <james@lrgmail.com>
2001-11-01 17:44           ` Jeff Eckermann <jeff_eckermann@yahoo.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox