How Can I Efficiently Create Dynamic Pivot Tables in PostgreSQL Using `crosstab()`?

A more efficient way to implement dynamic pivot tables using CASE and GROUP BY statements

Compared to the query provided in the article, using the tablefunc function in the crosstab() extension is a more efficient alternative.

Installation:

First, install the tablefunc extension if you haven’t already:

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

Basic Crosstab solution:

Simple crosstab solution for this scenario:

<code class="language-sql">SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM tbl_org
   ORDER BY bar, feh')
AS ct (bar text, val1 int, val2 int, val3 int);</code>

Synthetic category column:

If you don’t have an actual category column, you can use window functions to create a synthetic category column:

<code class="language-sql">SELECT * FROM crosstab(
   $$
   SELECT bar, val, feh
   FROM  (
      SELECT *, 'val' || row_number() OVER (PARTITION BY bar ORDER BY feh) AS val
      FROM tbl_org
      ) x
   ORDER BY 1, 2
   $$
 , $$VALUES ('val1'), ('val2'), ('val3')$$
) AS ct (bar text, val1 int, val2 int, val3 int);</code>

Dynamic Crosstab?

While creating a fully dynamic crosstab using plpgsql is challenging because of the limitations of dynamic return types. Here is a simpler test case example:

<code class="language-sql">SELECT * FROM crosstab('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2')
AS ct (row_name text, val1 int, val2 int, val3 int);</code>

tablefunc module:

The

tablefunc module provides a simplified approach:

<code class="language-sql">SELECT * FROM crosstab1('SELECT row_name, attrib, val::text FROM tbl ORDER BY 1,2');</code>

The above is the detailed content of How Can I Efficiently Create Dynamic Pivot Tables in PostgreSQL Using `crosstab()`?. For more information, please follow other related articles on the PHP Chinese website!