Home > mailing lists

Thread: pivoting data?

pivoting data?

From

Joy Smith

Date:

07 September 2011, 22:25:33

I finally understand why the query looks like it does, even though it is not what I wanted. Here is the setup:

Version

------------------------------------

"PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 32-bit"

Table Structure

------------------------------------

-- Table: modvalues

-- DROP TABLE modvalues;

CREATE TABLE modvalues

(

parties character varying,

baloons character varying,

color character varying,

dayofpurchase date,

someint serial NOT NULL,

amountpur integer,

CONSTRAINT wfe PRIMARY KEY (someint)

)

WITH (

OIDS=FALSE

);

ALTER TABLE modvalues OWNER TO postgres;

Test Data

------------------------------------

insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values ('1','big','red','1/1/2011',7);

insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values ('1','big','green','1/2/2011',14);

insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values ('1','big','blue','1/2/2011',3)

Query

------------------------------------

with a as (select distinct baloons, color,amountpur from modvalues),

b as (select baloons,color,amountpur from modvalues where dayofpurchase = '2011-01-01'),

c as (select baloons,color,amountpur from modvalues where dayofpurchase = '2011-01-02'),

d as (select baloons,color,amountpur from modvalues where dayofpurchase = '2011-01-03')

select

a.baloons,

a.color,

b.amountpur as "Jan First",

c.amountpur as "Jan Second",

d.amountpur as "Jan Third"

from

a left join b on a.baloons=b.baloons

left join c on a.baloons=c.baloons

left join d on a.baloons=d.baloons

Output

------------------------------------

"baloons";"color";"Jan First";"Jan Second";"Jan Third"

"big";"red";7;3;

"big";"red";7;14;

"big";"blue";7;3;

"big";"blue";7;14;

"big";"green";7;3;

"big";"green";7;14;

Issue

------------------------------------

I now see that it is putting 7's in for "Jan First" because I told it to put b.amountpur in there - but why are not rows 3-6 of the output blank for "Jan First" since there were not purchases made on that date for blue and green "color"'s? Is there a way to pivot the data so that it can lay out the data like this:

baloons color jan first jan second jan third

big red 7 null null

big green null 14 null

big blue null null 3

Re: pivoting data?

From

Chris Travers

Date:

07 September 2011, 22:38:53

On Wed, Sep 7, 2011 at 3:25 PM, Joy Smith <freestuffanddeals@gmail.com> wrote:
> I finally understand why the query looks like it does, even though it is not
> what I wanted.  Here is the setup:
>
> Version
> ------------------------------------
> "PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 32-bit"
>
> Table Structure
> ------------------------------------
> -- Table: modvalues
> -- DROP TABLE modvalues;
> CREATE TABLE modvalues
> (
>   parties character varying,
>   baloons character varying,
>   color character varying,
>   dayofpurchase date,
>   someint serial NOT NULL,
>   amountpur integer,
>   CONSTRAINT wfe PRIMARY KEY (someint)
> )
> WITH (
>   OIDS=FALSE
> );
> ALTER TABLE modvalues OWNER TO postgres;
>
>
> Test Data
> ------------------------------------
> insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values
> ('1','big','red','1/1/2011',7);
> insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values
> ('1','big','green','1/2/2011',14);
> insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values
> ('1','big','blue','1/2/2011',3)
>
>
> Query
> ------------------------------------
> with a as (select distinct baloons, color,amountpur from modvalues),
> b as (select baloons,color,amountpur from modvalues where dayofpurchase =
> '2011-01-01'),
> c as (select baloons,color,amountpur from modvalues where dayofpurchase =
> '2011-01-02'),
> d as (select baloons,color,amountpur from modvalues where dayofpurchase =
> '2011-01-03')
>
> select
> a.baloons,
> a.color,
> b.amountpur as "Jan First",
> c.amountpur as "Jan Second",
> d.amountpur as "Jan Third"
> from
> a left join b on a.baloons=b.baloons
> left join c on a.baloons=c.baloons
> left join d on a.baloons=d.baloons

Wondering if a CASE statement would be more efficient here:

SELECT baloons, color,
case when dayofpurchase = '2011-01-01' then amountpur AS 'Jan First'
ELSE NULL END,
case when dayofpurchase = '2011-01-02' then amountpur AS 'Jan Second'
ELSE NULL END,
case when dayofpurchase = '2011-01-03' then amountpur AS 'Jan Third'
ELSE NULL END
FROM modvalues;

Best Wishes,
Chris Travers

Re: pivoting data?

From

Joy Smith

Date:

08 September 2011, 12:15:36

Hello Chris, yes that does seem to be a lot cleaner - though it does one thing that is not right -

ie)

add to the previous data

insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values
> ('1','big','red','1/2/2011',4);

then the output looks like:

baloons color jan first jan second jan third

big red 7

big green 14

big blue 3

big red 4

instead of looking like this(having big red only show up once with the new value under jan second):

baloons color jan first jan second jan third

big red 7 4

big green 14

big blue 3

thank you for your help and ideas

On Wed, Sep 7, 2011 at 6:38 PM, Chris Travers <chris.travers@gmail.com> wrote:

On Wed, Sep 7, 2011 at 3:25 PM, Joy Smith <freestuffanddeals@gmail.com> wrote:
> I finally understand why the query looks like it does, even though it is not
> what I wanted. Here is the setup:
>
> Version
> ------------------------------------
> "PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 32-bit"
>
> Table Structure
> ------------------------------------
> -- Table: modvalues
> -- DROP TABLE modvalues;
> CREATE TABLE modvalues
> (
> parties character varying,
> baloons character varying,
> color character varying,
> dayofpurchase date,
> someint serial NOT NULL,
> amountpur integer,
> CONSTRAINT wfe PRIMARY KEY (someint)
> )
> WITH (
> OIDS=FALSE
> );
> ALTER TABLE modvalues OWNER TO postgres;
>
>
> Test Data
> ------------------------------------
> insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values
> ('1','big','red','1/1/2011',7);
> insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values
> ('1','big','green','1/2/2011',14);
> insert into modvalues (parties,baloons,color,dayofpurchase,amountpur) values
> ('1','big','blue','1/2/2011',3)
>
>
> Query
> ------------------------------------
> with a as (select distinct baloons, color,amountpur from modvalues),
> b as (select baloons,color,amountpur from modvalues where dayofpurchase =
> '2011-01-01'),
> c as (select baloons,color,amountpur from modvalues where dayofpurchase =
> '2011-01-02'),
> d as (select baloons,color,amountpur from modvalues where dayofpurchase =
> '2011-01-03')
>
> select
> a.baloons,
> a.color,
> b.amountpur as "Jan First",
> c.amountpur as "Jan Second",
> d.amountpur as "Jan Third"
> from
> a left join b on a.baloons=b.baloons
> left join c on a.baloons=c.baloons
> left join d on a.baloons=d.baloons

Wondering if a CASE statement would be more efficient here:

SELECT baloons, color,
case when dayofpurchase = '2011-01-01' then amountpur AS 'Jan First'
ELSE NULL END,
case when dayofpurchase = '2011-01-02' then amountpur AS 'Jan Second'
ELSE NULL END,
case when dayofpurchase = '2011-01-03' then amountpur AS 'Jan Third'
ELSE NULL END
FROM modvalues;

Best Wishes,
Chris Travers

Re: pivoting data?

From

Sim Zacks

Date:

08 September 2011, 13:44:38

<meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
    body p { margin-bottom: 0cm; margin-top: 0pt; }

  <body style="direction: ltr;"
    bidimailui-detected-decoding-type="latin-charset" bgcolor="#ffffff"
    text="#000000">
    On 09/08/2011 03:15 PM, Joy Smith wrote:
    <blockquote
cite="mid:CAJqhhiNtZKYxQjhXBAkGcXqopDghbUMB87crhoKDQ5HxgFrQaA@mail.gmail.com"
      type="cite">Hello Chris, yes that does seem to be a lot cleaner -
      though it does one thing that is not right - 
      ie)
      add to the previous data
       insert into modvalues
        (parties,baloons,color,dayofpurchase,amountpur) values
        > ('1','big','red','1/2/2011',4);




      then the output looks like:


      baloons     color        jan first       jan second    
jan
        third
      big             red            7
      big             green                
         14 
      big                 blue              
           3 
      big                  red            
              4 


      instead of looking like this(having big red only show up once
        with the new value under jan second):



        baloons     color        jan first       jan second    
jan
          third
        big             red            7    
               4 
        big             green                
         14 
        big                 blue            
             3 





      thank you for your help and ideas

    You'll want to group by baloons,color and probably take a sum of
    each of the case statements.

    Sim

Re: pivoting data?

From

Joy Smith

Date:

08 September 2011, 14:52:14

>You'll want to group by baloons,color and probably take a sum of each of the case statements.

>Sim

yes, hard to believe I did not notice that. Thank you, the working query is:

select baloons, color,

max(case when dayofpurchase = '2011-01-01' then amountpur ELSE NULL END) as "first",

max(CASE when dayofpurchase = '2011-01-02' then amountpur ELSE NULL END) as "second",

max(CASE when dayofpurchase = '2011-01-03' then amountpur ELSE NULL END) as "third"

from modvalues

group by baloons, color