PostgreSQL动态PIVOT实现：按指定related_id生成周维度动态列

针对需求——根据指定related_id将对应的week转为动态列，展示每个product_code在各周的qty值，以下是主流数据库的实现方案：

目标结果示例（当related_id=24时） #

SQL Server 实现 #

动态SQL版本（适配任意数量的week值） #

DECLARE @related_id INT = 24;
DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 获取需转为列的week值（按升序）
SELECT @cols = STRING_AGG(QUOTENAME(week), ', ')
FROM (
    SELECT DISTINCT week
    FROM your_table_name
    WHERE related_id = @related_id
    ORDER BY week
) AS week_list;

-- 拼接并执行动态PIVOT语句
SET @sql = N'
SELECT product_code, ' + @cols + '
FROM (
    SELECT product_code, week, qty
    FROM your_table_name
    WHERE related_id = ' + CAST(@related_id AS NVARCHAR) + '
) AS source_data
PIVOT (
    SUM(qty) -- 因每个(product_code, week)组合唯一，SUM/MAX/AVG效果一致
    FOR week IN (' + @cols + ')
) AS pivot_table;';

EXEC sp_executesql @sql;

注：STRING_AGG适用于SQL Server 2017及以上版本，旧版本可替换为FOR XML PATH拼接列名的写法。

MySQL 实现 #

MySQL无原生PIVOT函数，需通过动态拼接CASE语句实现：

SET @related_id = 24;
SET @cols = NULL;

-- 生成每个week对应的CASE语句
SELECT GROUP_CONCAT(DISTINCT CONCAT(
    'MAX(CASE WHEN week = ''', week, ''' THEN qty END) AS `', week, '`'
) ORDER BY week) INTO @cols
FROM your_table_name
WHERE related_id = @related_id;

-- 拼接并执行动态SQL
SET @sql = CONCAT('
SELECT product_code, ', @cols, '
FROM your_table_name
WHERE related_id = ', @related_id, '
GROUP BY product_code
ORDER BY product_code;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 实现 #

方法1：使用crosstab函数（需先安装扩展） #

首先确保tablefunc扩展已安装：

CREATE EXTENSION IF NOT EXISTS tablefunc;

然后执行动态行转列：

WITH params AS (SELECT 24 AS related_id),
week_list AS (
    SELECT DISTINCT week::TEXT
    FROM your_table_name, params
    WHERE related_id = params.related_id
    ORDER BY week
)
SELECT *
FROM crosstab(
    'SELECT product_code, week::TEXT, qty
     FROM your_table_name
     WHERE related_id = (SELECT related_id FROM params)
     ORDER BY 1, 2',
    'SELECT week FROM week_list'
) AS ct (product_code TEXT, ' || (SELECT string_agg(week, ' TEXT, ') FROM week_list) || ' TEXT);

方法2：动态SQL拼接 #

DO $$
DECLARE
    target_related_id INT := 24;
    cols TEXT;
    sql TEXT;
BEGIN
    -- 获取列名（转成标识符格式）
    SELECT string_agg(DISTINCT quote_ident(week::TEXT), ', ') INTO cols
    FROM your_table_name
    WHERE related_id = target_related_id
    ORDER BY week;

    -- 拼接PIVOT语句
    sql := format('
        SELECT product_code, %s
        FROM (
            SELECT product_code, week::TEXT, qty
            FROM your_table_name
            WHERE related_id = %L
        ) AS source
        PIVOT (
            SUM(qty)
            FOR week IN (%s)
        ) AS pivot_table;', cols, target_related_id, cols);

    -- 执行动态SQL
    EXECUTE sql;
END $$;

内容的提问来源于stack exchange，提问作者Ahmed Kolsi