SQL数据处理：将Split_indicator=1行的Others值追加至前置0值行

需求说明 #

需将有序数据中，Split_indicator为1的行的Others字段值，追加到其前最近的Split_indicator为0的行的Others字段中，并移除所有Split_indicator为1的行。

输入数据 #

seq No  name    adress  Split_indicator Others
-------------------------------------------------
1   |Sample Data | Sample Data | 0 | Other test data 1
2   |Sample Data | Sample Data | 0 | Other test data 2
3   |Sample Data | Sample Data | 0 | Other test data 3
4   |Sample Data | Sample Data | 1 | Other test data 4
5   |Sample Data | Sample Data | 1 | Other test data 5
6   |Sample Data | Sample Data | 1 | Other test data 6
7   |Sample Data | Sample Data | 1 | Other test data 7
8   |Sample Data | Sample Data | 1 | Other test data 8
9   |Sample Data | Sample Data | 0 | Other test data 9
10  |Sample Data | Sample Data | 0 | Other test data 10
11  |Sample Data | Sample Data | 1 | Other test data 11
12  |Sample Data | Sample Data | 1 | Other test data 12
13  |Sample Data | Sample Data | 1 | Other test data 13
14  |Sample Data | Sample Data | 0 | Other test data 14

目标输出 #

seq No  name    adress  Split_indicator Others
------------------------------------------------
1   | Sample Data | Sample Data | 0 | Other test data 1
2   | Sample Data | Sample Data | 0 | Other test data 2
3   | Sample Data | Sample Data | 0 | Other test data 3,Other test data 4,Other test data 5,Other test data 6,Other test data 7,Other test data 8
9   | Sample Data | Sample Data | 0 | Other test data 9
10  | Sample Data | Sample Data | 0 | Other test data 10,Other test data 11,Other test data 12,Other test data 13
14  | Sample Data | Sample Data | 0 | Other test data 14

测试数据创建SQL #

--drop table #temp
create table #temp (seq int, name varchar(100),adress varchar (100), Split_indicator varchar(10),Others varchar(1000))
GO

Insert into #temp values(1,'Sample Data','Sample Data',0,'Other test data 1' )
Insert into #temp values(2,'Sample Data','Sample Data',0,'Other test data 2' )
Insert into #temp values(3,'Sample Data','Sample Data',0,'Other test data 3' )
Insert into #temp values(4,'Sample Data','Sample Data',1,'Other test data 4' )
Insert into #temp values(5,'Sample Data','Sample Data',1,'Other test data 5' )
Insert into #temp values(6,'Sample Data','Sample Data',1,'Other test data 6' )
Insert into #temp values(7,'Sample Data','Sample Data',1,'Other test data 7' )
Insert into #temp values(8,'Sample Data','Sample Data',1,'Other test data 8' )
Insert into #temp values(9,'Sample Data','Sample Data',0,'Other test data 9' )
Insert into #temp values(10,'Sample Data','Sample Data',0,'Other test data 10' )
Insert into #temp values(11,'Sample Data','Sample Data',1,'Other test data 11' )
Insert into #temp values(12,'Sample Data','Sample Data',1,'Other test data 12' )
Insert into #temp values(13,'Sample Data','Sample Data',1,'Other test data 13' )
Insert into #temp values(14,'Sample Data','Sample Data',0,'Other test data 14' )
GO

select * from #temp

解决方案SQL #

WITH cte AS (
    SELECT 
        *,
        -- 为每行标记最近的前置Split_indicator=0的行的seq
        MAX(CASE WHEN Split_indicator = '0' THEN seq END) OVER (ORDER BY seq ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_seq
    FROM #temp
)
SELECT 
    t.seq,
    t.name,
    t.adress,
    t.Split_indicator,
    -- 按顺序拼接同组内的Others字段
    STRING_AGG(c.Others, ',') WITHIN GROUP (ORDER BY c.seq) AS Others
FROM #temp t
JOIN cte c ON t.seq = c.group_seq
WHERE t.Split_indicator = '0'
GROUP BY t.seq, t.name, t.adress, t.Split_indicator
ORDER BY t.seq;

说明 #

1. 借助CTE中的窗口函数，为每一行确定其归属的前置最近Split_indicator=0的行的seq（记为group_seq）；
2. 将原表中所有Split_indicator=0的行与CTE关联，通过STRING_AGG函数按顺序拼接同组内的所有Others值；
3. 最终分组排序后得到符合要求的结果。

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