Oracle 21c 供应商数据行转置（Pivot）实现求助

正确实现方案 #

方法1：条件聚合

SELECT
    MAX(CASE WHEN vendor = 'United co' THEN category END) AS "United co",
    MAX(CASE WHEN vendor = 'ali dist co' THEN category END) AS "ali dist co",
    MAX(CASE WHEN vendor = 'marian srl' THEN category END) AS "marian srl"
FROM (
    SELECT
        vendor,
        category,
        -- 为每个供应商的品类生成排序后的行号
        ROW_NUMBER() OVER (PARTITION BY vendor ORDER BY category) AS rn
    FROM prob
) t
-- 按行号分组，每个行号对应输出一行
GROUP BY rn
ORDER BY rn;

方法2：PIVOT 语法

SELECT "United co", "ali dist co", "marian srl"
FROM (
    SELECT
        vendor,
        category,
        ROW_NUMBER() OVER (PARTITION BY vendor ORDER BY category) AS rn
    FROM prob
)
PIVOT (
    -- 每个行号下取对应供应商的品类（max不影响，因每个rn在供应商下唯一）
    MAX(category)
    -- 指定转置为列的供应商名称
    FOR vendor IN (
        'United co' AS "United co",
        'ali dist co' AS "ali dist co",
        'marian srl' AS "marian srl"
    )
)
ORDER BY rn;

原尝试问题分析 #

• LISTAGG 方法：将每个供应商的所有品类合并为单个字符串，无法实现多行展示需求；
• 第二个 CASE WHEN 方法：错误使用GROUP BY vendor, rn，导致按供应商+行号分组，输出结构与期望不符，应改为仅按rn分组；
• 第三个 PIVOT 方法：转置对象错误（用seqnum作为列而非vendor），逻辑完全偏离需求。

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