如何通过SQL将多列统计结果转置为指定行列格式

要实现将「月份为行、指标为列」转成「指标为行、月份为列」，需要分两步操作：先将指标列拆分为行（Unpivot），再将月份行转为列（Pivot）。以下提供通用兼容方案和数据库专属方案，解决你之前尝试UNPIVOT/UNION ALL未成功的问题。

方法1：条件聚合（通用所有SQL数据库） #

这是兼容性最强的方案，无需依赖数据库特定语法，适合列数较多的场景：

-- 第一步：先按月份聚合原始数据
WITH aggregated_data AS (
    select
        YR_MO,
        sum(order_volume) as Sales,
        sum(transaction_count) as Transactions,
        sum(items_shipped) as Items,
        sum(freight_charge) as ShippingCost,
        sum(tax_incurred) as Tax
    from consolidated_data
    group by YR_MO
),
-- 第二步：将指标列拆分为行（替代UNPIVOT的通用写法）
unpivoted AS (
    SELECT YR_MO, 'Sales' AS Metric, Sales AS Value FROM aggregated_data
    UNION ALL
    SELECT YR_MO, 'Transactions' AS Metric, Transactions AS Value FROM aggregated_data
    UNION ALL
    SELECT YR_MO, 'Items' AS Metric, Items AS Value FROM aggregated_data
    UNION ALL
    SELECT YR_MO, 'Shipping Cost' AS Metric, ShippingCost AS Value FROM aggregated_data
    UNION ALL
    SELECT YR_MO, 'Tax' AS Metric, Tax AS Value FROM aggregated_data
)
-- 第三步：将月份转为列，按指标分组聚合
SELECT
    Metric,
    MAX(CASE WHEN YR_MO = '202301' THEN Value END) AS "202301",
    MAX(CASE WHEN YR_MO = '202302' THEN Value END) AS "202302",
    MAX(CASE WHEN YR_MO = '202303' THEN Value END) AS "202303",
    -- 按需添加所有需要展示的月份列
    MAX(CASE WHEN YR_MO = '202406' THEN Value END) AS "202406"
FROM unpivoted
GROUP BY Metric
ORDER BY Metric;

注意事项 #

• 如果YR_MO是日期类型而非字符串，需调整CASE判断逻辑，例如Oracle用TO_CHAR(YR_MO, 'YYYYMM') = '202301'，SQL Server用FORMAT(YR_MO, 'yyyyMM') = '202301'。
• 若月份数量动态变化，可结合动态SQL自动生成列（见下文补充）。

方法2：PIVOT语法（仅支持SQL Server、Oracle、PostgreSQL 11+） #

如果你的数据库支持PIVOT/UNPIVOT专属语法，代码会更简洁：

WITH aggregated_data AS (
    select
        YR_MO,
        sum(order_volume) as Sales,
        sum(transaction_count) as Transactions,
        sum(items_shipped) as Items,
        sum(freight_charge) as ShippingCost,
        sum(tax_incurred) as Tax
    from consolidated_data
    group by YR_MO
),
unpivoted AS (
    -- 用UNPIVOT直接拆分指标列
    SELECT YR_MO, Metric, Value
    FROM aggregated_data
    UNPIVOT (
        Value FOR Metric IN (Sales, Transactions, Items, ShippingCost, Tax)
    ) AS unpvt
)
-- 用PIVOT将月份转为列
SELECT 
    Metric,
    [202301], [202302], [202303], [202406]
FROM unpivoted
PIVOT (
    MAX(Value) FOR YR_MO IN ([202301], [202302], [202303], [202406])
) AS pvt
ORDER BY Metric;

补充：动态生成月份列（解决手动列名繁琐问题） #

如果月份会随数据新增，可使用动态SQL自动生成所有月份列，以SQL Server为例：

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

-- 自动获取所有唯一月份并生成列名
SELECT @cols = STRING_AGG(QUOTENAME(YR_MO), ', ')
FROM (SELECT DISTINCT YR_MO FROM consolidated_data) AS months;

-- 构建动态执行的SQL语句
SET @query = N'
WITH aggregated_data AS (
    select
        YR_MO,
        sum(order_volume) as Sales,
        sum(transaction_count) as Transactions,
        sum(items_shipped) as Items,
        sum(freight_charge) as ShippingCost,
        sum(tax_incurred) as Tax
    from consolidated_data
    group by YR_MO
),
unpivoted AS (
    SELECT YR_MO, Metric, Value
    FROM aggregated_data
    UNPIVOT (
        Value FOR Metric IN (Sales, Transactions, Items, ShippingCost, Tax)
    ) AS unpvt
)
SELECT Metric, ' + @cols + N'
FROM unpivoted
PIVOT (
    MAX(Value) FOR YR_MO IN (' + @cols + N')
) AS pvt
ORDER BY Metric;
';

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

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