pivoting data?

Started by Joy Smithover 14 years ago5 messagesgeneral

freestuffanddeals@gmail.com

over 14 years ago

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

?

chris.travers@gmail.com

over 14 years ago

In reply to: Joy Smith (#1)

Re: pivoting data?

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

freestuffanddeals@gmail.com

over 14 years ago

In reply to: Chris Travers (#2)

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.travers@gmail.com>wrote:

Show quoted text

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

sim@compulab.co.il

over 14 years ago

In reply to: Joy Smith (#3)

Re: pivoting data?

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html style="direction: ltr;">
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<style>body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<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 - 
<div>ie)</div>
<div>add to the previous data</div>
<div> insert into modvalues
(parties,baloons,color,dayofpurchase,amountpur) values<br>
> ('1','big','red','1/2/2011',4);<br>
<br>
</div>
<div><br>
</div>
<div>then the output looks like:</div>
<div><br>
</div>
<div>baloons     color        jan first       jan second     jan
third</div>
<div>big             red            7</div>
<div>big             green                           14</div>
<div>big                 blue                           3</div>
<div>big                  red                            4</div>
<div><br>
</div>
<div>instead of looking like this(having big red only show up once
with the new value under jan second):</div>
<div><br>
</div>
<div>
<div>baloons     color        jan first       jan second     jan
third</div>
<div>big             red            7                     4</div>
<div>big             green                           14</div>
<div>big                 blue                           3</div>
<div><br>
</div>
</div>
<div><br>
</div>
<div>thank you for your help and ideas</div>
</blockquote>
You'll want to group by baloons,color and probably take a sum of
each of the case statements.<br>
<br>
Sim<br>
</body>
</html>

freestuffanddeals@gmail.com

over 14 years ago

In reply to: Joy Smith (#3)

Re: pivoting data?

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