Oracle SQL Developer中行转列实现方案咨询

针对你的需求，使用PIVOT函数是最简洁高效的实现方式，自连接虽然可行，但当需要支持7组列时会导致代码冗余且维护性差，因此优先推荐PIVOT方案。

前提说明 #

Oracle中不允许结果集出现重复列名，因此将你期望输出中重复的Product ID和Expiry Date调整为带序号后缀的列名（如Product ID_1、Expiry Date_1），更符合数据库规范。

具体实现代码 #

假设你的表名为customer_products，执行以下SQL即可得到目标结果：

WITH ranked_products AS (
    SELECT 
        "Customer Number",
        "Product ID",
        "Expiry Date",
        -- 为每个客户的产品生成序号，最多支持7个
        ROW_NUMBER() OVER (PARTITION BY "Customer Number" ORDER BY "Product ID") AS product_seq
    FROM customer_products
)
SELECT 
    "Customer Number",
    -- 提取7组产品ID和过期日期，不足的自动显示为NULL
    "1_PROD" AS "Product ID_1",
    "1_EXP" AS "Expiry Date_1",
    "2_PROD" AS "Product ID_2",
    "2_EXP" AS "Expiry Date_2",
    "3_PROD" AS "Product ID_3",
    "3_EXP" AS "Expiry Date_3",
    "4_PROD" AS "Product ID_4",
    "4_EXP" AS "Expiry Date_4",
    "5_PROD" AS "Product ID_5",
    "5_EXP" AS "Expiry Date_5",
    "6_PROD" AS "Product ID_6",
    "6_EXP" AS "Expiry Date_6",
    "7_PROD" AS "Product ID_7",
    "7_EXP" AS "Expiry Date_7"
FROM ranked_products
PIVOT (
    MAX("Product ID") AS PROD,
    MAX("Expiry Date") AS EXP
    FOR product_seq IN (1,2,3,4,5,6,7)
)
ORDER BY "Customer Number";

代码说明 #

1. CTE部分（ranked_products）：使用ROW_NUMBER()函数为每个客户的产品分配唯一序号，这里按Product ID排序，你也可以根据需求调整为按Expiry Date排序。
2. PIVOT部分：将每个序号对应的Product ID和Expiry Date聚合为单独的列，使用MAX()是因为PIVOT必须搭配聚合函数，而每个序号对应唯一一条数据，MAX()不会改变结果。
3. 列别名：将PIVOT生成的带序号的列重命名为更易读的格式，不足7个产品的客户，后续列会自动显示为NULL，符合你的需求。

自连接方案（不推荐） #

如果一定要用自连接，需要多次关联表并过滤序号，但代码会非常冗长，例如实现3组列的示例（扩展到7组需重复相同逻辑）：

SELECT 
    c1."Customer Number",
    c1."Product ID" AS "Product ID_1",
    c1."Expiry Date" AS "Expiry Date_1",
    c2."Product ID" AS "Product ID_2",
    c2."Expiry Date" AS "Expiry Date_2",
    c3."Product ID" AS "Product ID_3",
    c3."Expiry Date" AS "Expiry Date_3"
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY "Customer Number" ORDER BY "Product ID") AS seq
    FROM customer_products
) c1
LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY "Customer Number" ORDER BY "Product ID") AS seq
    FROM customer_products
) c2 ON c1."Customer Number" = c2."Customer Number" AND c2.seq = 2
LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY "Customer Number" ORDER BY "Product ID") AS seq
    FROM customer_products
) c3 ON c1."Customer Number" = c3."Customer Number" AND c3.seq = 3
WHERE c1.seq = 1
ORDER BY c1."Customer Number";

可以看到，当扩展到7组时需要写7次子查询和JOIN，维护成本很高，因此优先选择PIVOT方案。

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