SQL SERVER 拆分列为多行

--创建测试表
create table #temp (seq int identity,names varchar(200))
insert into #temp(names)
values(‘张三,李四‘),
(‘中国,美国,巴西‘),
(‘深圳,上海,北京,广州,哈尔滨‘),
(‘足球,篮球,乒乓球,台球‘)

 1 ;with cte as(
 2 select 0 as n
 3 union all
 4 select N+1 from cte where n<100
 5 )
 6 ,idx as (
 7 select a.seq,b.n,ROW_NUMBER() over (partition by a.seq order by b.n) as id,‘,‘+a.names+‘,‘ as names
 8 from #temp a
 9     inner join cte b on b.n<=len(‘,‘+a.names+‘,‘ )
10 where SUBSTRING(‘,‘+a.names+‘,‘,b.n,1)=‘,‘
11 )
12 select a.seq,substring(a.names,a.n+1,b.n-a.n-1) as name
13 from idx a
14     inner join idx b on a.seq=b.seq and a.id=b.id-1