PostgreSQL Crosstab Query

Question

Hi all,

Does any one know how to create crosstab queries in PostgreSQL?

For example I have the following table:

Section    Status    Count
A          Active    1
A          Inactive  2
B          Active    4
B          Inactive  5

I would like the query to return the following crosstab:

Section    Active    Inactive
A          1         2
B          4         5

Is this possible?

Thanks!

Answer 1

Sorry this isn't complete because I can't test it here, but it may get you off in the right direction. I'm translating from something I use that makes a similar query:

select mt.section, mt1.count as Active, mt2.count as Inactive
from mytable mt
left join (select section, count from mytable where status='Active')mt1
on mt.section = mt1.section
left join (select section, count from mytable where status='Inactive')mt2
on mt.section = mt2.section
group by mt.section,
         mt1.count,
         mt2.count
order by mt.section asc;

The code I'm working from is:

select m.typeID, m1.highBid, m2.lowAsk, m1.highBid - m2.lowAsk as diff, 100*(m1.highBid - m2.lowAsk)/m2.lowAsk as diffPercent
from mktTrades m
   left join (select typeID,MAX(price) as highBid from mktTrades where bid=1 group by typeID)m1
   on m.typeID = m1.typeID
   left join (select typeID,MIN(price) as lowAsk  from mktTrades where bid=0 group by typeID)m2
   on m1.typeID = m2.typeID
group by m.typeID, 
         m1.highBid, 
         m2.lowAsk
order by diffPercent desc;

which will return a typeID, the highest price bid and the lowest price asked and the difference between the two (a positive difference would mean something could be bought for less than it can be sold).

Answer 2

You can use the crosstab function - http://www.postgresql.org/docs/current/static/tablefunc.html

In your case, I believe it would look something like this:

CREATE TABLE t (Section CHAR(1), Status VARCHAR(10), Count integer);
GO

INSERT INTO t VALUES ('A', 'Active', 1);
INSERT INTO t VALUES ('A', 'Inactive', 2);
INSERT INTO t VALUES ('B', 'Active', 4);
INSERT INTO t VALUES ('B', 'Inactive', 5);

SELECT  row_name AS Section,
        category_1::integer AS Active,
        category_2::integer AS Inactive
FROM crosstab('select section::text, status, count::text from t',2) AS ct (row_name text, category_1 text, category_2 text);

Answer 3

SELECT section,
       SUM(CASE status WHEN 'Active' THEN count ELSE 0 END) AS active,
       SUM(CASE status WHEN 'Inactive' THEN count ELSE 0 END) AS inactive
FROM t
GROUP BY section