银行交易数据集SQL Server转换：合并多行明细适配Power BI报表

针对你遇到的银行交易数据分散问题（主交易行带日期，后续明细行日期为NULL），可以通过窗口函数分组+聚合函数的方式实现数据整理，最终得到适配Power BI的结构化数据。

完整SQL代码（适用于SQL Server 2017+） #

假设原始数据存储在表BankTransactions中，执行以下查询：

WITH TransactionGroups AS (
    SELECT 
        *,
        -- 给每笔主交易及其明细分配唯一组ID：遇到非NULL日期则组号递增
        SUM(CASE WHEN Date IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY (SELECT NULL)) AS GroupID
    FROM BankTransactions
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY MAX(Date) DESC) AS Transaction_Key,
    MAX(Date) AS Date,
    MAX(CASE WHEN Date IS NOT NULL THEN Transaction_Details END) AS Transaction_Details,
    -- 合并明细行的交易描述为Other_Details
    STRING_AGG(CASE WHEN Date IS NULL THEN Transaction_Details END, ', ') AS Other_Details,
    MAX(Debit) AS Debit,
    MAX(Credit) AS Credit
FROM TransactionGroups
GROUP BY GroupID
ORDER BY Transaction_Key;

代码逻辑说明 #

1. TransactionGroups CTE：

   • 用SUM() OVER()窗口函数生成GroupID：每遇到一行带非NULL日期的主交易，累计值加1，后续所有日期为NULL的明细行会继承这个组ID，实现主交易与明细的关联。

2. 主查询聚合：

   • ROW_NUMBER()：按交易日期倒序生成唯一的Transaction_Key，对应示例中的序号。
   • MAX(Date)：提取组内唯一的非NULL日期（主交易行的日期）。
   • MAX(CASE...)：提取组内主交易行的Transaction_Details（明细行该字段无有效信息）。
   • STRING_AGG()：将组内所有明细行的Transaction_Details用逗号拼接成Other_Details。
   • MAX(Debit/Credit)：提取组内主交易行的金额（明细行金额为NULL，MAX会自动取非NULL值）。

兼容旧版本SQL Server（2016及以下） #

如果你的SQL Server版本不支持STRING_AGG，可以用FOR XML PATH替代字符串聚合：

WITH TransactionGroups AS (
    SELECT 
        *,
        SUM(CASE WHEN Date IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY (SELECT NULL)) AS GroupID
    FROM BankTransactions
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY MAX(Date) DESC) AS Transaction_Key,
    MAX(Date) AS Date,
    MAX(CASE WHEN Date IS NOT NULL THEN Transaction_Details END) AS Transaction_Details,
    -- 用FOR XML PATH拼接明细描述
    STUFF((
        SELECT ', ' + Transaction_Details
        FROM TransactionGroups t2
        WHERE t2.GroupID = t1.GroupID AND t2.Date IS NULL
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Other_Details,
    MAX(Debit) AS Debit,
    MAX(Credit) AS Credit
FROM TransactionGroups t1
GROUP BY GroupID
ORDER BY Transaction_Key;

测试验证 #

将你提供的示例数据插入临时表后执行上述查询，会直接得到你期望的结构化结果，可直接导入Power BI制作每日支出追踪报表。

内容的提问来源于stack exchange，提问作者Sergiu SA sas96