PostgreSQL Crosstab Query

The currently accepted answer seems outdated.

• The variant of the function crosstab(text, integer) is outdated. The second integer parameter is ignored. I quote the current manual:

crosstab(text sql, int N) ...

Obsolete version of crosstab(text). The parameter N is now ignored, since the number of value columns is always determined by the calling query

• Needless casting and renaming.

• It fails if a row does not have all attributes. There is a safe variant with two text parameters dealing properly with missing attributes.

• ORDER BY is required. Frankly, the accepted answer is just incorrect. I quote the manual here:

In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered

(Only applicable for the one-parameter form of crosstab(), which is used there.)

Proper answer

Install the additional module tablefunc which provides the function crosstab() once per database. Since PostgreSQL 9.1 you can use CREATE EXTENSION for that:

CREATE EXTENSION tablefunc;

Improved test case

CREATE TEMP TABLE t (
  section   text
 ,status    text
 ,ct        integer -- don't use "count" as column name.
);

INSERT INTO t VALUES 
 ('A', 'Active', 1), ('A', 'Inactive', 2)
,('B', 'Active', 4), ('B', 'Inactive', 5)
                   , ('C', 'Inactive', 7);  -- no row for C with 'Active'

• count is a reserved word, don't use it as column name.

Simple form - not fit for missing attributes

crosstab() with one parameter.

SELECT *
FROM   crosstab(
      'SELECT section, status, ct
       FROM   t
       ORDER  BY 1,2')  -- needs to be "ORDER BY 1,2" here
AS ct ("Section" text, "Active" int, "Inactive" int);

Returns:

 Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |      7 |

• No need for casting and renaming
• Note the incorrect result for C: the value 7 is filled in for the first column.

Safe form

crosstab() with two parameters.

SELECT * FROM crosstab(
       'SELECT section, status, ct
        FROM   t
        ORDER  BY 1,2'  -- could also just be "ORDER BY 1" here

      ,$$VALUES ('Active'::text), ('Inactive')$$)
AS ct ("Section" text, "Active" int, "Inactive" int);

Returns:

 Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |        |        7

• Note the correct result for C.

• The second parameter can be any query that returns one row per attribute matching the order of the column definition at the end. Often you will want to query distinct attributes from the underlying table like this:

  'SELECT DISTINCT attribute FROM tbl ORDER BY 1'
  

  That's in the manual.

  Since you have to spell out all columns in a column definition list anyway, it is regularly more efficient to provide a short list in a VALUES expression like I demonstrate:

  $$VALUES ('Active'::text), ('Inactive')$$)
  

  That's not in the manual.

• I used dollar quoting to make quoting easier.

Advanced examples

Pivot on Multiple Columns using Tablefunc
Dynamic alternative to pivot with CASE and GROUP BY

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

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).