Oracle动态Pivot查询求助：实现可变列数的月份金额转置

嘿，这个动态转置的需求确实很常见，尤其是当Period的数量不固定时，硬编码列名肯定行不通。下面我针对主流数据库给出对应的解决方案，你可以根据自己使用的数据库选择：

1. SQL Server 解决方案 #

SQL Server支持PIVOT运算符，结合动态SQL可以轻松实现可变列的转置：

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

-- 第一步：获取所有唯一的Period值，拼接成列名列表
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Period)
                      FROM (SELECT DISTINCT Period FROM YourTableName) AS Periods
                      ORDER BY Period
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 第二步：动态生成PIVOT查询语句，确保每个原始行对应一行结果，无值填0
SET @query = N'
SELECT Task, Project, ' + 
STUFF((SELECT ', COALESCE(' + QUOTENAME(Period) + ', 0) AS ' + QUOTENAME(Period)
       FROM (SELECT DISTINCT Period FROM YourTableName) AS Periods
       ORDER BY Period
       FOR XML PATH(''), TYPE
      ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + N'
FROM (
    SELECT 
        Task,
        Project,
        Period,
        Amt,
        -- 给每个原始行加唯一标识，确保转置后每行对应原始行
        ROW_NUMBER() OVER(PARTITION BY Task, Project, Period ORDER BY (SELECT 0)) AS rn
    FROM YourTableName
) AS SourceData
PIVOT (
    SUM(Amt)
    FOR Period IN (' + @cols + N')
) AS PivotTable;';

-- 执行动态SQL
EXEC sp_executesql @query;

注：这里用ROW_NUMBER()确保每个原始行在转置后单独成一行，COALESCE把NULL替换成0，完全匹配你想要的结果格式。

2. MySQL 解决方案 #

MySQL没有内置的PIVOT，但可以通过GROUP_CONCAT动态生成CASE语句来实现：

SET @sql = NULL;

-- 动态生成每个Period对应的CASE列
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'SUM(CASE WHEN Period = ''',
        Period,
        ''' THEN Amt ELSE 0 END) AS `',
        Period,
        '`'
    )
) INTO @sql
FROM YourTableName;

-- 拼接完整查询语句，按原始行维度分组
SET @sql = CONCAT(
    'SELECT Task, Project, ', @sql, ' 
     FROM YourTableName 
     GROUP BY Task, Project, Period'
);

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

3. PostgreSQL 解决方案 #

PostgreSQL可以用动态SQL拼接，或者借助crosstab函数（需先安装扩展）：

方式一：动态SQL拼接 #

DO $$
DECLARE
    cols TEXT;
    case_cols TEXT;
BEGIN
    -- 获取所有唯一的Period列名
    SELECT string_agg(DISTINCT quote_ident(Period), ', ') INTO cols
    FROM YourTableName;
    
    -- 生成每个列的COALESCE处理语句
    SELECT string_agg(DISTINCT 'COALESCE(' || quote_ident(Period) || ', 0) AS ' || quote_ident(Period), ', ') INTO case_cols
    FROM YourTableName;

    -- 生成并执行动态查询
    EXECUTE format('
        SELECT Task, Project, %s
        FROM (
            SELECT Task, Project, Period, Amt
            FROM YourTableName
        ) AS SourceData
        PIVOT (
            SUM(Amt) FOR Period IN (%s)
        ) AS PivotTable
    ', case_cols, cols);
END $$;

方式二：使用crosstab（需先启用扩展） #

-- 先启用tablefunc扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 动态生成crosstab查询
DO $$
DECLARE
    periods TEXT;
    col_defs TEXT;
    query TEXT;
BEGIN
    SELECT string_agg(DISTINCT quote_literal(Period), ', ') INTO periods
    FROM YourTableName;
    
    SELECT string_agg(DISTINCT quote_ident(Period) || ' numeric', ', ') INTO col_defs
    FROM YourTableName;

    query := format('
        SELECT * FROM crosstab(
            ''SELECT Task, Project, Period, Amt FROM YourTableName ORDER BY 1,2,3'',
            ''SELECT DISTINCT Period FROM YourTableName ORDER BY 1''
        ) AS ct(Task text, Project text, %s);
    ', col_defs);

    EXECUTE query;
END $$;

不管用哪种数据库，核心思路都是先动态获取所有需要转置的列名，再拼接成完整的SQL语句执行，这样就能完美适配Period数量不固定的场景啦。

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