SQL Server秘籍：数据分隔解密，数据库处理新境界！

-- 创建临时表
if object_id('tempdb..#Tab') is not null drop table #Tab;
if object_id('tempdb..#test') is not null drop table #test;
go

create table #Tab ([Col1] int, [COl2] nvarchar(5));
go

insert #Tab 
select 1, N'a,b,c' union all 
select 2, N'd,e' union all 
select 3, N'f';
go

-- 按照逗号拆分
select  
    a.COl1, b.Col2, row_number() over (partition by COl1 order by COl1) as ID 
into #test
from  
    (select Col1, COl2=convert(xml,' <root> <v>'+replace(COl2,',',' </v> <v>')+' </v> </root>') from #Tab) a 
outer apply 
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v') C(v)) b;

-- 重新组合相同 COl1 值的数据
select  
    c.Col1, 
    case when COl2='' then NULL else LEFT(COl2, LEN(COl2)-1) end as COl2  
from (
    select Col1,
        (
            select COl2+','
            from #test 
            where COl1 = b.COl1 
            order by ID asc 
            for xml path('')
        ) as COl2
    from #test b  
    group by Col1
) c;

-- 结果展示
select * from #test;
select * from #Tab;

-- 清理临时表
if object_id('tempdb..#Tab') is not null drop table #Tab;
if object_id('tempdb..#test') is not null drop table #test;

-- 创建临时表
if object_id('tempdb..#Tab') is not null drop table #Tab;
if object_id('tempdb..#test') is not null drop table #test;
go

create table #Tab ([Col1] int, [COl2] nvarchar(5));
go

insert #Tab 
select 1, N'a,b,c' union all 
select 2, N'd,e' union all 
select 3, N'f';
go

-- 使用 STRING_SPLIT 拆分数据
select  
    Col1, 
    value as Col2, 
    row_number() over (partition by Col1 order by Col1) as ID 
into #test
from #Tab
cross apply string_split(COl2, ',');

-- 重新组合相同 COl1 值的数据
select  
    Col1, 
    string_agg(Col2, ',') as Col2
from #test
group by Col1;

-- 结果展示
select * from #test;
select * from #Tab;

-- 清理临时表
if object_id('tempdb..#Tab') is not null drop table #Tab;
if object_id('tempdb..#test') is not null drop table #test;