How to Create Crosstab Queries in PostgreSQL Using the tablefunc Extension?

Detailed explanation of PostgreSQL cross-table query: Use tablefunc extension to create pivot table

This article will introduce in detail how to use the tablefunc extension to create cross-table queries (Crosstab Queries) in PostgreSQL to implement pivot table conversion of data.

Create cross-table query

Cross-table query converts data into tabular format, where rows represent categories and columns represent values. PostgreSQL implements this functionality through the tablefunc extension.

Double parameter cross-table query syntax:

SELECT *
FROM crosstab(
  'SELECT row_name, category, value
   FROM base_table
   ORDER BY 1, 2',
  'SELECT DISTINCT attribute FROM base_table ORDER BY 1',
) AS ct (row_name text, column_1 type_1, ..., column_n type_n);

Handling missing attributes:

If there are missing attributes in the base table, you can use the second parameter to specify which attributes to include in the cross-table. The value of missing attributes will be empty.

Handle redundant input lines:

• Single parameter form: Extra lines will be discarded, older lines first.
• Two-argument form: Later lines will overwrite existing values ​​for the same category and attribute combination.

Advanced cross-table query:

• Multi-column pivot: Use multiple ORDER BY clauses in the first parameter query.
• Dynamic pivot alternative: Use the CASE and GROUP BY statements.

Use crosstabview in psql (PostgreSQL 9.6 and above):

Use the crosstabview meta command in psql to perform cross-table queries:

\crosstabview

Example query:

Consider the following example table:

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

To create a cross-table with Section as row and Status as column:

SELECT *
FROM crosstab(
  'SELECT section, status, COUNT(*) FROM tbl GROUP BY 1, 2',
  'SELECT DISTINCT status FROM tbl ORDER BY 1',
) AS ct (Section text, Active int, Inactive int);

Result:

<code>Section    Active    Inactive
A          1         2
B          4         5</code>

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