Re: pivoting data?

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