How to Create Crosstab Queries in PostgreSQL Using the `crosstab()` Function?

PostgreSQL cross table query: use crosstab() function to create a cross table

PostgreSQL cross -table query can convert data to more easy -to -read table formats, where the value is paid according to the category, which represents different attribute values. You can use the tablefunc function in the module to create a cross -table query. crosstab()

Improve test cases

Consider the following table:

We want to create a cross -table query and return to the following form:

<code>Section    Status    Count
A          Active    1
A          Inactive  2
B          Active    4
B          Inactive  5
C          Inactive  7</code>

Security form

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

To create this cross -table query, you can use the dual parameter form:

crosstab() This form is "safe" because it ensures that each attribute is allocated to its dedicated column, even if there are excess input banks. In this example, the value of Section C is filled in the Inactive column, but the Active column keeps NULL.

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

Processing the excess input bank

The two forms of

have different ways to deal with excess input banks. Single parameters form the available values ​​from left to right, and discard excess values. The dual parameter forms each input value to its dedicated column, covering any previous allocation. Advanced examples

crosstab() Cross table query can be used for various high -level scenarios, for example:

Multi -column perspective Use a dynamic alternative scheme (such as case and group by) for perspective

PostgreSQL 9.6 introduced the

yuan command in its interactive terminal PSQL. It allows you to run a cross -table query and display the results in a table format.

crosstabview

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