现在有表中数据,想把字段appIds的值分隔开
语句:
SELECT a.Id,b.AppId FROM
(
SELECT id,AppId=CAST('<v>'+REPLACE(AppIds,',','</v><v>')+'</v>' AS xml)
FROM test
) a
OUTER APPLY (SELECT AppId=T.C.value('.','varchar(50)') FROM a.AppId.nodes('/v') AS T(C)
) b
GROUP BY a.id,b.AppId
其中
SELECT id,AppId=CAST('<v>'+REPLACE(AppIds,',','</v><v>')+'</v>' AS xml) FROM test
直接把appids转换成xml格式:
最终结果: