PostgreSQL Crosstab Query
Install the additional module tablefunc
once per database, which provides the function crosstab()
. Since Postgres 9.1 you can use CREATE EXTENSION
for that:
CREATE EXTENSION IF NOT EXISTS tablefunc;
Improved test case
CREATE TABLE tbl ( section text , status text , ct integer -- "count" is a reserved word in standard SQL);INSERT INTO tbl VALUES ('A', 'Active', 1), ('A', 'Inactive', 2), ('B', 'Active', 4), ('B', 'Inactive', 5) , ('C', 'Inactive', 7); -- ('C', 'Active') is missing
Simple form - not fit for missing attributes
crosstab(text)
with 1 input parameter:
SELECT *FROM crosstab( 'SELECT section, status, ct FROM tbl 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 value7
is filled in for the first column. Sometimes, this behavior is desirable, but not for this use case. - The simple form is also limited to exactly three columns in the provided input query: row_name, category, value. There is no room for extra columns like in the 2-parameter alternative below.
Safe form
crosstab(text, text)
with 2 input parameters:
SELECT *FROM crosstab( 'SELECT section, status, ct FROM tbl 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 typically more efficient to provide a short list in aVALUES
expression like demonstrated:$$VALUES ('Active'::text), ('Inactive')$$)
Or (not in the manual):
$$SELECT unnest('{Active,Inactive}'::text[])$$ -- short syntax for long lists
I used dollar quoting to make quoting easier.
You can even output columns with different data types with
crosstab(text, text)
- as long as the text representation of the value column is valid input for the target type. This way you might have attributes of different kind and outputtext
,date
,numeric
etc. for respective attributes. There is a code example at the end of the chaptercrosstab(text, text)
in the manual.
db<>fiddle here
Advanced examples
Pivot on Multiple Columns using Tablefunc - also demonstrating mentioned "extra columns"
\crosstabview
in psql
Postgres 9.6 added this meta-command to its default interactive terminal psql. You can run the query you would use as first crosstab()
parameter and feed it to \crosstabview
(immediately or in the next step). Like:
db=> SELECT section, status, ct FROM tbl \crosstabview
Similar result as above, but it's a representation feature on the client side exclusively. Input rows are treated slightly differently, hence ORDER BY
is not required. Details for \crosstabview
in the manual. There are more code examples at the bottom of that page.
Related answer on dba.SE by Daniel Vérité (the author of the psql feature):
The previously accepted answer is outdated.
The variant of the function
crosstab(text, integer)
is outdated. The secondinteger
parameter is ignored. I quote the current manual:crosstab(text sql, int N)
...Obsolete version of
crosstab(text)
. The parameterN
is now ignored, since the number of value columns is always determined by the calling queryNeedless casting and renaming.
It fails if a row does not have all attributes. See safe variant with two input parameters above to handle missing attributes properly.
ORDER BY
is required in the one-parameter form ofcrosstab()
. The manual:In practice the SQL query should always specify
ORDER BY 1,2
to ensure that the input rows are properly ordered
SELECT section, SUM(CASE status WHEN 'Active' THEN count ELSE 0 END) AS active, --here you pivot each status value as a separate column explicitly SUM(CASE status WHEN 'Inactive' THEN count ELSE 0 END) AS inactive --here you pivot each status value as a separate column explicitlyFROM tGROUP BY section
You can use the crosstab()
function of the additional module tablefunc - which you have to install once per database. Since PostgreSQL 9.1 you can use CREATE EXTENSION
for that:
CREATE EXTENSION tablefunc;
In your case, I believe it would look something like this:
CREATE TABLE t (Section CHAR(1), Status VARCHAR(10), Count integer);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 InactiveFROM crosstab('select section::text, status, count::text from t',2) AS ct (row_name text, category_1 text, category_2 text);