sql server 一条sql语句将逗号分隔字符串转换为行
‘1,2,3,4,5,6’
转换为
1
2
3
4
5
6
------解决思路----------------------
; with cte(id) as
(
select '1,2,3,4,5,6'
)
Select
id=substring(id, b.number, charindex(',', id+',',b.number)-b.number)
from
cte a join master..spt_values b
ON b.type='p' AND b.number BETWEEN 1 AND LEN(a.id)
where
substring(','+a.id,b.number,1)=','