sql 某列逗号分割字段，拆分多条(一行变多行)

CREATE TABLE TempTable
(
    [ChestCode] NVARCHAR(1000)
)
Insert TempTable values('a,b,c,d')

SELECT distinct b.chestcode
FROM(
    SELECT  chestcode=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(chestcode)),',','</v><v>')+'</v></root>') 
    FROM TempTable   
) a 
OUTER APPLY(
    SELECT chestcode = C.v.value('.','NVARCHAR(MAX)') 
    FROM a.chestcode.nodes('/root/v') C(v)
) b

declare @str nvarchar(50)
set @str='a,b,c,d'
SELECT b.strString
FROM(
    select strString=CONVERT(XML,'<root><v>'+replace(RTRIM(LTRIM(@str)),',','</v><v>')+'</v></root>')
) a 
OUTER APPLY(
    SELECT strString = C.v.value('.','NVARCHAR(MAX)') 
    FROM a.strString.nodes('/root/v') C(v)
) b

 

例如一行数据： a,b,c,d

拆分成多行：

a

b

c

d