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 ?