How to Efficiently Perform a Crosstab Query in SQL?

Use CASE and GROUP BY to dynamically replace PIVOT

Question:

The data shown in the table below is organized in rows and columns. The goal is to convert this into a table with a dynamic number of columns, where each column represents a value grouped by a specified category.

id	feh	bar
1	10	A
2	20	A
3	3	B
4	4	B
5	5	C
6	6	D
7	7	D
8	8	D

Expected output:

bar	val1	val2	val3
A	10	20
B	3	4
C	5
D	6	7	8

Original query:

The following query uses CASE expressions and GROUP BY to achieve the desired results:

<code class="language-sql">SELECT bar, 
MAX(CASE WHEN abc."row" = 1 THEN feh ELSE NULL END) AS "val1",
MAX(CASE WHEN abc."row" = 2 THEN feh ELSE NULL END) AS "val2",
MAX(CASE WHEN abc."row" = 3 THEN feh ELSE NULL END) AS "val3"
FROM
(
  SELECT bar, feh, row_number() OVER (partition by bar) as row
  FROM "Foo"
 ) abc
GROUP BY bar </code>

Efficient crosstab alternative:

To improve efficiency and readability, you can use the crosstab function in the tablefunc module to implement a dynamic solution. Here's an example:

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

Handling multiple values:

For scenarios where there are multiple values ​​under the same category, the crosstab function can be extended to the following form:

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

Built-in crosstab function:

The tablefunc module also provides predefined crosstab functions for a specific number of columns:

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

These functions make calling simpler and handle text data by default.

Dynamic return type:

Although tablefunc simplifies the process, it has limitations in handling dynamic return types. To solve this problem, other methods can be considered, such as using PL/pgSQL functions or creating dynamic SQL statements.

The above is the detailed content of How to Efficiently Perform a Crosstab Query in SQL?. For more information, please follow other related articles on the PHP Chinese website!