如何实现按年月拼接的动态SQL数据透视？

假设你已经能通过拼接得到格式为YYYY-Mon的年月字段（比如命名为YearMonth），以下按主流数据库给出最优动态透视方案：

SQL Server 原生PIVOT方案 #

利用SQL Server的PIVOT语法结合动态列生成，性能最优、语法简洁：

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

-- 生成所有需要作为列的YearMonth值，用引号包裹并逗号分隔
SELECT @cols = STRING_AGG(QUOTENAME(YearMonth), ', ')
FROM (
    SELECT DISTINCT CONCAT(Yr, '-', LEFT(DATENAME(month, DATEADD(month, Mon-1, '1900-01-01')), 3)) AS YearMonth
    FROM your_table
) AS Months;

-- 拼接动态透视SQL并执行
SET @query = N'
SELECT LOC, ' + @cols + N'
FROM (
    SELECT LOC, 
           CONCAT(Yr, '-', LEFT(DATENAME(month, DATEADD(month, Mon-1, '1900-01-01')), 3)) AS YearMonth, 
           Amt
    FROM your_table
) AS SourceData
PIVOT (
    SUM(Amt) -- 因每个LOC-年月对应唯一Amt，SUM/MAX/MIN效果一致
    FOR YearMonth IN (' + @cols + N')
) AS PivotTable;';

EXEC sp_executesql @query;

说明：SQL Server 2017+可用STRING_AGG拼接列名，低版本可替换为FOR XML PATH方式生成列列表。

MySQL 动态CASE分组方案 #

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

SET @cols = NULL;

-- 生成每个年月对应的CASE分支片段
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'MAX(CASE WHEN YearMonth = ''', YearMonth, ''' THEN Amt END) AS `', YearMonth, '`'
    )
) INTO @cols
FROM (
    SELECT CONCAT(Yr, '-', DATE_FORMAT(STR_TO_DATE(Mon, '%c'), '%b')) AS YearMonth
    FROM your_table
) AS Months;

-- 拼接并执行动态SQL
SET @query = CONCAT(
    'SELECT LOC, ', @cols, '
     FROM (
         SELECT LOC, 
                CONCAT(Yr, '-', DATE_FORMAT(STR_TO_DATE(Mon, '%c'), '%b')) AS YearMonth, 
                Amt
         FROM your_table
     ) AS SourceData
     GROUP BY LOC;'
);

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

说明：若列数超过GROUP_CONCAT默认长度限制，可通过SET group_concat_max_len = 10240;调整阈值。

PostgreSQL 两种可选方案 #

方案1：专用crosstab函数 #

先启用tablefunc扩展，再调用专用透视函数：

CREATE EXTENSION IF NOT EXISTS tablefunc;

WITH months AS (
    SELECT DISTINCT CONCAT(Yr, '-', TO_CHAR(TO_DATE(Mon::text, 'MM'), 'Mon')) AS YearMonth
    FROM your_table
    ORDER BY YearMonth
)
SELECT 'SELECT * FROM crosstab(
    ''SELECT LOC, YearMonth, Amt FROM (
        SELECT LOC, CONCAT(Yr, ''''-''''||TO_CHAR(TO_DATE(Mon::text, ''''MM''''), ''''Mon'''') AS YearMonth, Amt
        FROM your_table
    ) AS SourceData ORDER BY 1,2'',
    ''SELECT DISTINCT YearMonth FROM (
        SELECT CONCAT(Yr, ''''-''''||TO_CHAR(TO_DATE(Mon::text, ''''MM''''), ''''Mon'''') AS YearMonth
        FROM your_table
    ) AS Months ORDER BY 1''
) AS ct(LOC text, ', string_agg(quote_ident(YearMonth)||' numeric', ', '), ');'
) INTO @query;

EXECUTE @query;

方案2：动态CASE分组 #

逻辑同MySQL，适配PostgreSQL语法：

WITH months AS (
    SELECT DISTINCT CONCAT(Yr, '-', TO_CHAR(TO_DATE(Mon::text, 'MM'), 'Mon')) AS YearMonth
    FROM your_table
)
SELECT 'SELECT LOC, '||string_agg('MAX(CASE WHEN YearMonth = '''||YearMonth||''' THEN Amt END) AS '||quote_ident(YearMonth), ', ')||'
FROM (
    SELECT LOC, CONCAT(Yr, ''''-''''||TO_CHAR(TO_DATE(Mon::text, ''''MM''''), ''''Mon'''') AS YearMonth, Amt
    FROM your_table
) AS SourceData
GROUP BY LOC;'
INTO @query;

EXECUTE @query;

方案选型建议 #

• SQL Server：优先用原生PIVOT方案，性能最优
• MySQL：动态CASE分组是唯一原生可行方案，适合固定120列的场景
• PostgreSQL：数据量大时选crosstab函数，小数据量用动态CASE更简洁

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