求助：仅用CASE语句转置行列并合并多表的SQL实现

一、修正CountValue转置逻辑 #

原代码的核心问题：

• GROUP BY中包含CountValue，导致每个CountValue值单独成组，无法实现聚合转置
• 多余的distinct（GROUP BY已自动实现分组去重）
• 语法错误：t1.CountsC后缺少逗号
• 不必要的ColCountE列（若仅标记类型，无需加入分组）

正确的CountValue转置代码：

WITH Counts AS (
    SELECT
        t1.CountsA,
        t1.CountsB,
        t1.CountsC,
        t1.CountsD,
        SUM(CASE WHEN Description = 'Arizona' THEN t1.CountValue END) AS ColArizona_Count,
        SUM(CASE WHEN Description = 'California' THEN t1.CountValue END) AS ColCalifornia_Count,
        SUM(CASE WHEN Description = 'Florida' THEN t1.CountValue END) AS ColFlorida_Count,
        SUM(CASE WHEN Description = 'Iowa' THEN t1.CountValue END) AS ColIowa_Count,
        SUM(CASE WHEN Description = 'Kansas' THEN t1.CountValue END) AS ColKansas_Count,
        SUM(CASE WHEN Description = 'Kentucky' THEN t1.CountValue END) AS ColKentucky_Count
    FROM Details t1
    GROUP BY t1.CountsA, t1.CountsB, t1.CountsC, t1.CountsD
)
SELECT * FROM Counts;

二、生成AmountA、AmountB转置表 #

按照相同逻辑，分别处理AmountA和AmountB：

AmountA转置代码 #

WITH AmtA AS (
    SELECT
        t1.CountsA,
        t1.CountsB,
        t1.CountsC,
        t1.CountsD,
        SUM(CASE WHEN Description = 'Arizona' THEN t1.AmountA END) AS ColArizona_AmtA,
        SUM(CASE WHEN Description = 'California' THEN t1.AmountA END) AS ColCalifornia_AmtA,
        SUM(CASE WHEN Description = 'Florida' THEN t1.AmountA END) AS ColFlorida_AmtA,
        SUM(CASE WHEN Description = 'Iowa' THEN t1.AmountA END) AS ColIowa_AmtA,
        SUM(CASE WHEN Description = 'Kansas' THEN t1.AmountA END) AS ColKansas_AmtA,
        SUM(CASE WHEN Description = 'Kentucky' THEN t1.AmountA END) AS ColKentucky_AmtA
    FROM Details t1
    GROUP BY t1.CountsA, t1.CountsB, t1.CountsC, t1.CountsD
)
SELECT * FROM AmtA;

AmountB转置代码 #

WITH AmtB AS (
    SELECT
        t1.CountsA,
        t1.CountsB,
        t1.CountsC,
        t1.CountsD,
        SUM(CASE WHEN Description = 'Arizona' THEN t1.AmountB END) AS ColArizona_AmtB,
        SUM(CASE WHEN Description = 'California' THEN t1.AmountB END) AS ColCalifornia_AmtB,
        SUM(CASE WHEN Description = 'Florida' THEN t1.AmountB END) AS ColFlorida_AmtB,
        SUM(CASE WHEN Description = 'Iowa' THEN t1.AmountB END) AS ColIowa_AmtB,
        SUM(CASE WHEN Description = 'Kansas' THEN t1.AmountB END) AS ColKansas_AmtB,
        SUM(CASE WHEN Description = 'Kentucky' THEN t1.AmountB END) AS ColKentucky_AmtB
    FROM Details t1
    GROUP BY t1.CountsA, t1.CountsB, t1.CountsC, t1.CountsD
)
SELECT * FROM AmtB;

三、合并三个转置表 #

以CountsA和CountsB为键进行INNER JOIN是可行的，但需注意：

• 若CountsA+CountsB无法唯一确定分组，需补充CountsC、CountsD到JOIN条件中，避免出现不匹配的分组数据
• 合并时需将三个表的转置列按需求拼接，保留所有分组字段

完整合并代码：

WITH Counts AS (
    SELECT
        t1.CountsA,
        t1.CountsB,
        t1.CountsC,
        t1.CountsD,
        SUM(CASE WHEN Description = 'Arizona' THEN t1.CountValue END) AS ColArizona_Count,
        SUM(CASE WHEN Description = 'California' THEN t1.CountValue END) AS ColCalifornia_Count,
        SUM(CASE WHEN Description = 'Florida' THEN t1.CountValue END) AS ColFlorida_Count,
        SUM(CASE WHEN Description = 'Iowa' THEN t1.CountValue END) AS ColIowa_Count,
        SUM(CASE WHEN Description = 'Kansas' THEN t1.CountValue END) AS ColKansas_Count,
        SUM(CASE WHEN Description = 'Kentucky' THEN t1.CountValue END) AS ColKentucky_Count
    FROM Details t1
    GROUP BY t1.CountsA, t1.CountsB, t1.CountsC, t1.CountsD
),
AmtA AS (
    SELECT
        t1.CountsA,
        t1.CountsB,
        t1.CountsC,
        t1.CountsD,
        SUM(CASE WHEN Description = 'Arizona' THEN t1.AmountA END) AS ColArizona_AmtA,
        SUM(CASE WHEN Description = 'California' THEN t1.AmountA END) AS ColCalifornia_AmtA,
        SUM(CASE WHEN Description = 'Florida' THEN t1.AmountA END) AS ColFlorida_AmtA,
        SUM(CASE WHEN Description = 'Iowa' THEN t1.AmountA END) AS ColIowa_AmtA,
        SUM(CASE WHEN Description = 'Kansas' THEN t1.AmountA END) AS ColKansas_AmtA,
        SUM(CASE WHEN Description = 'Kentucky' THEN t1.AmountA END) AS ColKentucky_AmtA
    FROM Details t1
    GROUP BY t1.CountsA, t1.CountsB, t1.CountsC, t1.CountsD
),
AmtB AS (
    SELECT
        t1.CountsA,
        t1.CountsB,
        t1.CountsC,
        t1.CountsD,
        SUM(CASE WHEN Description = 'Arizona' THEN t1.AmountB END) AS ColArizona_AmtB,
        SUM(CASE WHEN Description = 'California' THEN t1.AmountB END) AS ColCalifornia_AmtB,
        SUM(CASE WHEN Description = 'Florida' THEN t1.AmountB END) AS ColFlorida_AmtB,
        SUM(CASE WHEN Description = 'Iowa' THEN t1.AmountB END) AS ColIowa_AmtB,
        SUM(CASE WHEN Description = 'Kansas' THEN t1.AmountB END) AS ColKansas_AmtB,
        SUM(CASE WHEN Description = 'Kentucky' THEN t1.AmountB END) AS ColKentucky_AmtB
    FROM Details t1
    GROUP BY t1.CountsA, t1.CountsB, t1.CountsC, t1.CountsD
)
SELECT
    c.CountsA,
    c.CountsB,
    c.CountsC,
    c.CountsD,
    -- CountValue转置列
    c.ColArizona_Count,
    c.ColCalifornia_Count,
    c.ColFlorida_Count,
    c.ColIowa_Count,
    c.ColKansas_Count,
    c.ColKentucky_Count,
    -- AmountA转置列
    a.ColArizona_AmtA,
    a.ColCalifornia_AmtA,
    a.ColFlorida_AmtA,
    a.ColIowa_AmtA,
    a.ColKansas_AmtA,
    a.ColKentucky_AmtA,
    -- AmountB转置列
    b.ColArizona_AmtB,
    b.ColCalifornia_AmtB,
    b.ColFlorida_AmtB,
    b.ColIowa_AmtB,
    b.ColKansas_AmtB,
    b.ColKentucky_AmtB
FROM Counts c
INNER JOIN AmtA a 
    ON c.CountsA = a.CountsA 
    AND c.CountsB = a.CountsB 
    AND c.CountsC = a.CountsC 
    AND c.CountsD = a.CountsD
INNER JOIN AmtB b 
    ON c.CountsA = b.CountsA 
    AND c.CountsB = b.CountsB 
    AND c.CountsC = b.CountsC 
    AND c.CountsD = b.CountsD;

四、关键注意事项 #

• 确保CASE语句中的Description值与表中实际数据完全匹配（比如原代码中California 和Kansas 带空格，需检查数据是否包含该空格，避免匹配失败）
• 针对百万级数据，需为GROUP BY的列建立索引，提升查询性能
• 若需保留原表顺序，可在最终查询中添加ORDER BY子句，指定原表的排序字段

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