How to Dynamically Pivot Data in SQL Using crosstab()?

SQL dynamic pivot table: a dynamic alternative using the crosstab() function

Problem description:

Suppose there is a table containing data in the following format:

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

The goal is to convert this table into a more structured format, where each row represents a category (bar) and the corresponding values ​​(feh) are arranged into columns with column names such as val1, val2, etc. The desired output looks like this:

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

The traditional solution involves using CASE statements and GROUP BY clauses to pivot data. However, for tables with many categories, this approach can become very verbose and unwieldy.

Alternative:

The tablefunc module provides a more efficient and dynamic alternative to traditional solutions. By utilizing the crosstab() function, we can achieve the same results with a simpler, more maintainable query.

Solution:

Make sure the tablefunc module is installed, execute the following command once per database:

CREATE EXTENSION tablefunc;

Here is a basic crosstab query that solves the problem:

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);  --更多列？

In this query:

• The crosstab() function accepts two parameters: a subquery that selects the data and assigns dummy category values ​​to each row; and a list of column names (in this case, val1, val2, and val3).
The
• subquery selects the bar and feh columns from the original table, uses the row_number() window function to assign a dummy category value of 1 to each row, and sorts the data by bar and feh to ensure the correct order in the output.
• The result of the crosstab() function is assigned to a CTE (common table expression) named ct to make it easier to reference in subsequent SELECT statements.

Dynamic Crosstab:

While this basic approach works well, it may not work well when the number of categories (columns) is unknown in advance. To solve this problem, we can define a dynamic crosstab function that generates column names based on different values ​​in a specified category column.

The following query demonstrates how to create and use dynamic crosstab functions:

-- 创建动态crosstab函数
CREATE FUNCTION dynamic_crosstab(anyarray) RETURNS table AS $$
  DECLARE
    column_names text[];
    column_definitions text[];
    cte_name text;
  BEGIN
    -- 获取类别列的不同值
    column_names := ARRAY(SELECT DISTINCT unnest());
    -- 生成列定义
    column_definitions := ARRAY(SELECT STRING_AGG('"' || name || '" INT', ', ') FROM (SELECT unnest(column_names) AS name) AS subquery);
    cte_name := 'cte_' || md5(random()::text); -- 生成唯一的CTE名称
    EXECUTE FORMAT('CREATE TEMP TABLE %s (%s)', cte_name, column_definitions);
    -- 将数据插入CTE
    INSERT INTO %s SELECT * FROM crosstab();
    -- 返回CTE
    RETURN QUERY EXECUTE FORMAT('SELECT * FROM %s', cte_name);
  END;
$$ LANGUAGE plpgsql;

-- 使用动态crosstab函数
SELECT * FROM dynamic_crosstab(ARRAY['bar']);

This revised response provides a more detailed and accurate explanation of the SQL dynamic pivot table solution, including the creation and usage of a dynamic crosstab function. The code is formatted for better readability.

The above is the detailed content of How to Dynamically Pivot Data in SQL Using crosstab()?. For more information, please follow other related articles on the PHP Chinese website!