Oracle SQL Developer行转列需求：实现无重复列的指定输出

原始Purchased表数据 #

期望输出格式 #

实现SQL语句 #

SELECT
    Customer_ID,
    Last_Name,
    First_name,
    -- 生成产品购买日期列
    MAX(CASE WHEN Product = '4WD' THEN Purchased END) AS "4WD",
    MAX(CASE WHEN Product = 'Car' THEN Purchased END) AS "Car",
    MAX(CASE WHEN Product = 'Car a' THEN Purchased END) AS "Car a",
    MAX(CASE WHEN Product = 'Car b' THEN Purchased END) AS "Car b",
    MAX(CASE WHEN Product = 'SUV' THEN Purchased END) AS "SUV",
    MAX(CASE WHEN Product = 'Truck' THEN Purchased END) AS "Truck",
    -- 生成保修期限列
    MAX(CASE WHEN Product = '4WD' THEN Warranty END) AS "Warranty_4WD",
    MAX(CASE WHEN Product = 'Car' THEN Warranty END) AS "Warranty_Car",
    MAX(CASE WHEN Product = 'Car a' THEN Warranty END) AS "Warranty_CarA",
    MAX(CASE WHEN Product = 'Car b' THEN Warranty END) AS "Warraty_CarB",
    MAX(CASE WHEN Product = 'SUV' THEN Warranty END) AS "Warranty_SUV",
    MAX(CASE WHEN Product = 'Truck' THEN Warranty END) AS "Warranty_Truck"
FROM Purchased
GROUP BY Customer_ID, Last_Name, First_name
ORDER BY Customer_ID;

说明 #

• 采用条件聚合+GROUP BY的方式，通过CASE WHEN匹配每个产品类型，用MAX提取对应记录的值（因每个客户对应单个产品仅一条记录，MAX/MIN效果一致）。
• 相比PIVOT语法，这种方式更直观，能完全按照需求指定列名和顺序，避免PIVOT可能出现的列名混乱、重复问题。
• 列名包含空格或特殊格式时，需用双引号包裹，Oracle会保留原始格式与大小写。

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