PostgreSQL中实现行转列（支持动态新增周列）的简便方案

MySQL 实现方式 #

MySQL原生不支持自动动态PIVOT，需通过存储过程生成动态SQL实现：

存储过程代码 #

DELIMITER //
CREATE PROCEDURE dynamic_pivot_weeks()
BEGIN
    -- 拼接所有周数作为orders字段的转列语句
    SET @columns = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN week = ', week, ' THEN orders END) AS `', week, '`'))
    INTO @columns
    FROM your_table_name; -- 替换为你的实际表名

    -- 拼接其他字段（如示例中的...列）的转列语句
    SET @other_columns = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN week = ', week, ' THEN `...` END) AS `', week, '`'))
    INTO @other_columns
    FROM your_table_name;

    -- 生成完整查询语句
    SET @sql = CONCAT(
        'SELECT ''orders'' AS `week`, ', @columns, ' UNION ALL ',
        'SELECT ''...'' AS `week`, ', @other_columns
    );

    -- 执行动态SQL
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

使用方式 #

调用存储过程即可自动适配所有周数：

CALL dynamic_pivot_weeks();

新增周数后，再次调用会自动生成对应列。

PostgreSQL 实现方式 #

需先启用tablefunc扩展，再结合动态SQL实现：

步骤1：启用扩展 #

CREATE EXTENSION IF NOT EXISTS tablefunc;

动态查询代码 #

DO $$
DECLARE
    cols text;
BEGIN
    -- 获取所有周数列名
    SELECT string_agg(DISTINCT quote_ident(week::text), ', ') INTO cols
    FROM your_table_name; -- 替换为实际表名

    -- 生成并执行动态交叉表查询
    EXECUTE format(
        'SELECT * FROM crosstab(
            ''SELECT '' || quote_literal(''orders'') || '' AS row_name, week, orders FROM your_table_name
            UNION ALL
            SELECT '' || quote_literal(''...'') || '' AS row_name, week, '' || quote_ident(''...'') || '' FROM your_table_name'',
            ''SELECT DISTINCT week FROM your_table_name ORDER BY week''
        ) AS ct(row_name text, %s)',
        cols
    );
END $$;

SQL Server 实现方式 #

利用PIVOT结合动态SQL生成列列表：

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- 拼接所有周数列名
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(week)
                      FROM your_table_name
                      GROUP BY week
                      ORDER BY week
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 生成动态PIVOT查询
SET @query = N'
SELECT ''orders'' AS [week], ' + @cols + '
FROM (
    SELECT week, orders FROM your_table_name
) AS src
PIVOT (
    MAX(orders) FOR week IN (' + @cols + ')
) AS pvt
UNION ALL
SELECT ''...'' AS [week], ' + @cols + '
FROM (
    SELECT week, [....] FROM your_table_name -- 替换为实际的...字段名
) AS src
PIVOT (
    MAX([....]) FOR week IN (' + @cols + ')
) AS pvt';

EXEC sp_executesql @query;

注意事项 #

• 替换所有your_table_name为你的实际表名
• 若存在多个需转列的字段，需为每个字段重复转列逻辑
• 动态SQL需注意SQL注入风险，数字类型周数风险较低，字符串类型需用quote_ident或QUOTENAME处理

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