如何调整SQL查询实现按GL Code拆分发票金额的报表？

Document表将同一发票的多个GL Code及对应金额存储在同一行的多列（如GLCode1/Amount1、GLCode2/Amount2），导致查询结果出现大量空值。核心解决思路是将这些横向存储的列转换为纵向的行，同时过滤空值，再关联GL Code表完成报表生成。

方法1：使用UNION ALL（通用所有SQL数据库） #

兼容性最强，适合不支持专用列转行函数的数据库。假设Document表包含InvoiceID、InvoiceDate、Vendor、GLCode1、Amount1、GLCode2、Amount2、GLCode3、Amount3字段，GL Code表包含GLCode、GLDescription字段：

SELECT 
    d.InvoiceID,
    d.InvoiceDate,
    d.Vendor,
    gl.GLCode,
    gl.GLDescription,
    split.Amount
FROM Document d
CROSS JOIN (
    -- 拆分每组GL Code与金额，过滤空值
    SELECT d.GLCode1 AS GLCode, d.Amount1 AS Amount WHERE d.GLCode1 IS NOT NULL
    UNION ALL
    SELECT d.GLCode2 AS GLCode, d.Amount2 AS Amount WHERE d.GLCode2 IS NOT NULL
    UNION ALL
    SELECT d.GLCode3 AS GLCode, d.Amount3 AS Amount WHERE d.GLCode3 IS NOT NULL
) split
JOIN GLCode gl ON split.GLCode = gl.GLCode
ORDER BY d.InvoiceID, gl.GLCode;

说明 #

• 通过UNION ALL把每一组GL Code和金额拆分为独立行，WHERE条件直接过滤空GL Code，避免无效空值行。
• CROSS JOIN关联原表与拆分后的行，保留发票基础信息，最后关联GL Code表补充描述字段。

方法2：使用UNPIVOT（适用于SQL Server、Oracle等支持的数据库） #

如果数据库支持UNPIVOT语法，可以更简洁实现列转行：

SELECT 
    d.InvoiceID,
    d.InvoiceDate,
    d.Vendor,
    gl.GLCode,
    gl.GLDescription,
    d.Amount
FROM (
    SELECT 
        InvoiceID,
        InvoiceDate,
        Vendor,
        GLCode,
        Amount
    FROM Document
    -- 拆分金额列
    UNPIVOT (
        Amount FOR AmountCol IN (Amount1, Amount2, Amount3)
    ) upAmount
    -- 拆分GL Code列
    UNPIVOT (
        GLCode FOR CodeCol IN (GLCode1, GLCode2, GLCode3)
    ) upCode
    -- 确保GL Code与金额的对应关系（通过列名后缀匹配）
    WHERE RIGHT(AmountCol, 1) = RIGHT(CodeCol, 1)
      AND GLCode IS NOT NULL
) d
JOIN GLCode gl ON d.GLCode = gl.GLCode
ORDER BY d.InvoiceID, gl.GLCode;

说明 #

• 两次UNPIVOT分别处理金额和GL Code列，通过列名的数字后缀（如1、2）确保关联关系正确。
• 同样过滤空GL Code，避免无效数据。

关键注意事项 #

• 若Document表有更多GL Code/金额列，只需在UNION ALL或UNPIVOT的列列表中追加对应字段即可。
• 必须保证GL Code与对应金额的关联逻辑准确，避免出现金额与GL Code不匹配的错误。
• 始终过滤空GL Code，减少无效数据输出。

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