How to Create Crosstab Queries in PostgreSQL using the `tablefunc` module?

Detailed explanation of PostgreSQL crosstab query and tablefuncmodule application

This article will introduce in detail how to use the tablefunc module to create a crosstab query in PostgreSQL.

Install tablefuncmodule

First, you need to install the tablefunc extension:

<code class="language-sql">CREATE EXTENSION IF NOT EXISTS tablefunc;</code>

Example

Test form:

<code class="language-sql">CREATE TABLE tbl (
   section   text,
   status    text,
   ct        integer
);

INSERT INTO tbl VALUES 
  ('A', 'Active', 1), ('A', 'Inactive', 2),
  ('B', 'Active', 4), ('B', 'Inactive', 5),
  ('C', 'Inactive', 7);</code>

Target crosstab:

<code>Section | Active | Inactive
---------+--------+----------
A       |      1 |        2
B       |      4 |        5
C       |        |        7</code>

crosstabFunction

Single parameter form (restricted):

<code class="language-sql">SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- 必须为 "ORDER BY 1,2"
   ) AS ct ("Section" text, "Active" int, "Inactive" int);</code>

Double parameter form (recommended):

<code class="language-sql">SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- 也可简化为 "ORDER BY 1"

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

The impact of multi-line input

Single parameter form:

• Populate the available values ​​column from left to right.
• Excess values ​​will be discarded.
• Older input lines take precedence.

Double parameter form:

• Assign each input value to its dedicated column.
• Overwrite any previous assignment.
• Later input lines take precedence.

Advanced Examples

• Use Tablefunc for multi-column pivoting
• Dynamic perspective alternative using CASE and GROUP BY

crosstabview

in psql

PostgreSQL 9.6 introduced this meta-command in psql:

<code class="language-sql">db=> SELECT section, status, ct FROM tbl \crosstabview</code>

The above is the detailed content of How to Create Crosstab Queries in PostgreSQL using the `tablefunc` module?. For more information, please follow other related articles on the PHP Chinese website!