schone schone - 4 months ago 69
SQL Question

PostgreSQL Crosstab Query

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?

Answer

Install the additional module tablefunc once per database, which provides the function crosstab(). 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'

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 (except for pre-defined crosstabN() variants), it is regularly more efficient to provide a short list in a VALUES expression like I demonstrate:

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

    Or:

    $$SELECT unnest('{Active,Inactive}'::text[])$$ -- shorter for long lists
    

    That's not in the manual.

  • I used dollar quoting to make quoting easier.

Advanced examples


Corrective

The previously accepted answer by Jeremiah is 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. 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.)

Comments