Re: pivoting data?

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(dot)travers(at)gmail(dot)com>wrote:

> On Wed, Sep 7, 2011 at 3:25 PM, Joy Smith <freestuffanddeals(at)gmail(dot)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
>