如何在SQL（Coginity）中将单列转置为唯一成员表

原始数据 #

注：原始数据中Mra应为Mar（三月），以下SQL会按原始拼写处理，若已修正则对应调整条件即可。

期望结果 #

注：你提供的期望结果中Mem 3应为Mem 2，属于笔误。

解决方案 #

方法1：CASE WHEN + 聚合函数（通用兼容写法） #

这是所有SQL数据库都支持的通用行转列写法，Coginity完全适用。通过CASE匹配每个Month值，结合聚合函数（SUM或MAX，因每个Mem ID+Month组合唯一，两者效果一致）提取对应列的数据：

SELECT
    "Mem ID",
    SUM(CASE WHEN "Month" = 'Jan' THEN "Total" ELSE 0 END) AS Jan,
    SUM(CASE WHEN "Month" = 'Feb' THEN "Total" ELSE 0 END) AS Feb,
    SUM(CASE WHEN "Month" = 'Mra' THEN "Total" ELSE 0 END) AS Mar,
    SUM(CASE WHEN "Month" = 'April' THEN "Total" ELSE 0 END) AS April
FROM
    your_table_name -- 替换为你的实际表名
GROUP BY
    "Mem ID"
ORDER BY
    "Mem ID";

若确认每个Mem ID对应Month只有一条记录，用MAX替代SUM更直观：

SELECT
    "Mem ID",
    MAX(CASE WHEN "Month" = 'Jan' THEN "Total" END) AS Jan,
    MAX(CASE WHEN "Month" = 'Feb' THEN "Total" END) AS Feb,
    MAX(CASE WHEN "Month" = 'Mra' THEN "Total" END) AS Mar,
    MAX(CASE WHEN "Month" = 'April' THEN "Total" END) AS April
FROM
    your_table_name
GROUP BY
    "Mem ID"
ORDER BY
    "Mem ID";

方法2：PIVOT函数（若Coginity支持） #

如果Coginity兼容ANSI SQL的PIVOT语法，可使用更简洁的写法：

SELECT
    "Mem ID",
    Jan,
    Feb,
    Mar,
    April
FROM
    your_table_name
PIVOT (
    SUM("Total") -- 或MAX("Total")
    FOR "Month" IN ('Jan' AS Jan, 'Feb' AS Feb, 'Mra' AS Mar, 'April' AS April)
) AS pivot_table
ORDER BY
    "Mem ID";

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