SQL SERVER 字符拆分列为多行

注：先学习sql server里的递归CTE。

假设有兴趣表Hobbys

Name	Hobby
小张	篮球，足球，羽毛球

 

 

 

Name	Hobby
小张	篮球
小张	足球
小张	羽毛球

 

 

 

 

 

采用【递归cte】

with tempHobbys as (select Name,Hobby,charindex(',',Hobby)-1 endindex,0 startindex from Hobbys 
union all 
select Name,Hobby,charindex(',',Hobby,endindex+2)-1 endindex,endindex+2 startindex from tempHobbys where endindex>-1),
with tempHobbys 2 as (select Name,Hobby,(case when endindex<0 then len(Hobby) else Hobby end) endindex,startindex from tempHobbys)
select Name,substring(Hobby,startindex,endindex-startindex+1) Hobby from tempHobbys2