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.trav...@gmail.com>wrote: > On Wed, Sep 7, 2011 at 3:25 PM, Joy Smith <freestuffandde...@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 >