matrix query?

Started by Jim Mercerover 23 years ago2 messagesgeneral
Jump to latest
#1Jim Mercer
jim@reptiles.org

this is probably a more generic SQL question than a postgres specific one, but:

CREATE TABLE data
(
customer varchar(10),
widgets int,
day int
);

INSERT INTO DATA values('CustA', 5, 1);
INSERT INTO DATA values('CustA', 4, 2);
INSERT INTO DATA values('CustA', 9, 3);
INSERT INTO DATA values('CustB', 8, 1);
INSERT INTO DATA values('CustB', 3, 2);
INSERT INTO DATA values('CustB', 1, 3);
INSERT INTO DATA values('CustB', 1, 3);
INSERT INTO DATA values('CustC', 2, 1);
INSERT INTO DATA values('CustC', 2, 1);
INSERT INTO DATA values('CustC', 7, 2);
INSERT INTO DATA values('CustC', 1, 3);

what kind of select can i do to get:

Day CustA CustB CustC
1 5 8 4
2 4 3 7
3 9 2 1

--
[ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
[ I want to live forever, or die trying. ]

#2Daniel Lucazeau
pgsql@ajornet.com
In reply to: Jim Mercer (#1)
Re: matrix query?

Bonjour Jim,

Le jeudi 1 aoᅵt 2002 ᅵ 21:07:32, vous ᅵcriviez :

JM> what kind of select can i do to get:

JM> Day CustA CustB CustC
JM> 1 5 8 4
JM> 2 4 3 7
JM> 3 9 2 1

This one :
SELECT day,
SUM(CASE WHEN customer='CustA' THEN widgets ELSE 0 END) AS "CustA",
SUM(CASE WHEN customer='CustB' THEN widgets ELSE 0 END) AS "CustB",
SUM(CASE WHEN customer='CustC' THEN widgets ELSE 0 END) AS "CustC",
SUM(1) as Total
FROM DATA
GROUP BY day
See : http://php.weblogs.com/ADOdb_manual#pivot

--
Cordialement,
Daniel mailto:pgsql@ajornet.com