sqlserver apply

IF OBJECT_ID(‘tb‘) IS NOT NULL DROP TABLE tb

go

CREATE TABLE tb(name VARCHAR(10),value VARCHAR(200))

INSERT INTO tb VALUES(‘张三‘,‘1,2,3,4‘)
INSERT INTO tb VALUES(‘李四‘,‘a,b,c,d‘)



SELECT a.[name],b.[value]
FROM (SELECT [name],[value]=CAST(‘<v>‘+REPLACE([value],‘,‘,‘</v><v>‘)+‘</v>‘ AS xml) FROM tb) a
OUTER APPLY (SELECT [value]=T.C.value(‘.‘,‘varchar(50)‘) FROM a.[value].nodes(‘/v‘) AS T(C)) b