如何将SQL查询结果按ITEM_ID合并为多列输出？

问题背景 #

你当前执行的查询：

SELECT * FROM PIM_USP where item_ID IN (SELECT Item_ID from PIM_ITEM where PIM_ITEM.brick_ID=10002084)

得到的输出是每行对应一个USP条目：

ITEM_ID USP_NO Value
2616761 1 Type Bijtafel
2616761 2 Materiaal Steen
2616761 3 
2616761 4 
2616761 5 
5037554 1 Materiaal Geïmpregneerd hout
5037554 2 
5037554 3 

希望转换为每个ITEM_ID一行，不同USP_NO对应到USP1、USP2等列：

ITEM_ID USP1 USP2
2616761 Type Bijtafel Materiaal Steen
5037554 Materiaal Geïmpregneerd hout 

解决方案：条件聚合（跨数据库通用） #

这是最通用的行转列方案，几乎所有主流数据库都支持，核心思路是用CASE语句结合聚合函数（比如MAX）把同一ITEM_ID的不同USP值聚合到一行：

SELECT
    ITEM_ID,
    -- 提取USP_NO=1的值作为USP1
    MAX(CASE WHEN USP_NO = 1 THEN Value END) AS USP1,
    -- 提取USP_NO=2的值作为USP2
    MAX(CASE WHEN USP_NO = 2 THEN Value END) AS USP2,
    -- 如果需要更多USP列，继续添加对应的CASE语句即可
    MAX(CASE WHEN USP_NO = 3 THEN Value END) AS USP3,
    MAX(CASE WHEN USP_NO = 4 THEN Value END) AS USP4,
    MAX(CASE WHEN USP_NO = 5 THEN Value END) AS USP5
FROM (
    -- 先获取需要的基础数据（关联PIM_USP和PIM_ITEM）
    SELECT pu.ITEM_ID, pu.USP_NO, pu.Value
    FROM PIM_USP pu
    INNER JOIN PIM_ITEM pi 
        ON pu.ITEM_ID = pi.Item_ID
    WHERE pi.brick_ID = 10002084
) AS usp_data
-- 按ITEM_ID分组，把同一物品的所有USP聚合到一行
GROUP BY ITEM_ID;

为什么用MAX？

因为每个ITEM_ID + USP_NO组合只会有一条记录，MAX（或者MIN、SUM都可以）的作用是过滤掉空值，把有效的Value保留到对应的列中，刚好符合你不需要显示空USP列的需求。

可选方案：使用PIVOT（部分数据库支持） #

如果你的数据库支持PIVOT语法（比如SQL Server、Oracle），也可以用更简洁的写法：

SELECT ITEM_ID, [1] AS USP1, [2] AS USP2, [3] AS USP3, [4] AS USP4, [5] AS USP5
FROM (
    SELECT pu.ITEM_ID, pu.USP_NO, pu.Value
    FROM PIM_USP pu
    INNER JOIN PIM_ITEM pi 
        ON pu.ITEM_ID = pi.Item_ID
    WHERE pi.brick_ID = 10002084
) AS usp_data
PIVOT (
    MAX(Value)
    -- 指定要转成列的USP_NO值
    FOR USP_NO IN ([1], [2], [3], [4], [5])
) AS pivot_result;

不过这个方案的兼容性不如条件聚合，如果你需要跨数据库运行，优先选条件聚合的写法。

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