分隔符上的SQL拆分逗号分隔列SQL 逗号分隔将一行拆成多行

select * into #tmpTable FROM (
  VALUES (1, 'aa,bb'),
(2 ,'aaa,bbb,ccc')
) AS T(id ,value)

select *  from #tmpTable

SELECT A.id, B.value FROM
(
SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') 
FROM #tmpTable
) A OUTER APPLY
(
  SELECT value = N.v.value('.', 'varchar(100)') 
  FROM A.[value].nodes('/root/v') N(v)
) B

drop table #tmpTable