如何在PLSQL中使用CASE或PIVOT实现重复发票号数据的单列展示

场景假设 #

假设你的原始数据表（记为InvoiceData）结构如下：

期望结果是将同一发票号的多条记录合并为一行，按顺序展示各条明细（或合并为单列）。

方法一：使用CASE语句实现静态列转换 #

适合已知每个发票最多重复次数的场景，手动定义对应列：

WITH RankedInvoices AS (
    SELECT 
        InvoiceNo,
        Product,
        Quantity,
        -- 按发票号分组，为每条记录分配序号
        ROW_NUMBER() OVER (PARTITION BY InvoiceNo ORDER BY Product) AS RowSeq
    FROM InvoiceData
)
SELECT 
    InvoiceNo,
    -- 提取第1条记录的产品和数量
    MAX(CASE WHEN RowSeq = 1 THEN Product END) AS Product_1,
    MAX(CASE WHEN RowSeq = 1 THEN Quantity END) AS Quantity_1,
    -- 提取第2条记录的产品和数量
    MAX(CASE WHEN RowSeq = 2 THEN Product END) AS Product_2,
    MAX(CASE WHEN RowSeq = 2 THEN Quantity END) AS Quantity_2,
    -- 提取第3条记录的产品和数量（根据实际最大重复数扩展）
    MAX(CASE WHEN RowSeq = 3 THEN Product END) AS Product_3,
    MAX(CASE WHEN RowSeq = 3 THEN Quantity END) AS Quantity_3
FROM RankedInvoices
GROUP BY InvoiceNo;

说明 #

• 先通过ROW_NUMBER()为每个发票号下的记录编号，区分重复项的顺序。
• 用CASE语句结合MAX()聚合，将不同序号的记录映射到对应列。
• 如果发票的最大重复数超过3，只需继续添加对应CASE分支即可。

方法二：使用PIVOT语句实现列转换 #

PIVOT适合结构化的列转换场景，静态PIVOT需要明确目标列名：

WITH RankedInvoices AS (
    SELECT 
        InvoiceNo,
        Product,
        Quantity,
        -- 生成动态列名前缀
        'Product_' + CAST(ROW_NUMBER() OVER (PARTITION BY InvoiceNo ORDER BY Product) AS VARCHAR) AS ProductCol,
        'Quantity_' + CAST(ROW_NUMBER() OVER (PARTITION BY InvoiceNo ORDER BY Product) AS VARCHAR) AS QuantityCol
    FROM InvoiceData
)
SELECT 
    InvoiceNo,
    Product_1, Quantity_1, Product_2, Quantity_2, Product_3, Quantity_3
FROM (
    -- 将Product和Quantity列拆分为键值对
    SELECT InvoiceNo, ColName, ColValue
    FROM RankedInvoices
    UNPIVOT (
        ColValue FOR ColName IN (Product, Quantity)
    ) AS Unpvt
    -- 替换为动态列名
    CROSS APPLY (
        SELECT CASE ColName WHEN 'Product' THEN ProductCol ELSE QuantityCol END AS TargetCol, ColValue
    ) AS ColMap
) AS PivotSource
-- 按动态列名进行PIVOT
PIVOT (
    MAX(ColValue) FOR TargetCol IN (Product_1, Quantity_1, Product_2, Quantity_2, Product_3, Quantity_3)
) AS PivotResult;

说明 #

• 先为每个重复项生成唯一的列名（如Product_1）。
• 通过UNPIVOT将多列转为键值对，再用CROSS APPLY映射到目标列名。
• 最后用PIVOT将键值对转回结构化列。
• 若需要处理未知数量的重复项，可结合动态SQL生成PIVOT列列表。

方法三：将重复项合并为单列（字符串聚合） #

如果需求是将同一发票的所有明细合并到单个列中，使用字符串聚合函数更直接：

SQL Server 2017+ / Azure SQL #

SELECT 
    InvoiceNo,
    STRING_AGG('产品：' + Product + '，数量：' + CAST(Quantity AS VARCHAR), '；') AS 明细汇总
FROM InvoiceData
GROUP BY InvoiceNo;

MySQL #

SELECT 
    InvoiceNo,
    GROUP_CONCAT('产品：', Product, '，数量：', Quantity SEPARATOR '；') AS 明细汇总
FROM InvoiceData
GROUP BY InvoiceNo;

说明 #

• 该方法将同一发票的所有记录拼接为一个字符串，适合需要紧凑展示的场景。

注意事项 #

1. 根据你的实际数据表结构（列名、数据类型）调整上述SQL中的表名和列名。
2. 若发票的重复次数不固定，动态SQL结合PIVOT是更灵活的选择，但需要数据库支持动态SQL语法。
3. ROW_NUMBER()中的ORDER BY子句可根据你的需求调整排序逻辑（如按日期、数量等）。

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