SQL Server2008中CROSS APPLY的应用范例(三) - 将一个或多个字段内用逗号分隔的内容分成多条记录

本文链接：https://blog.csdn.net/ghlfllz/article/details/50175357

SQL Server2008中CROSS APPLY的应用范例(三) - 将一个或多个字段内用逗号分隔的内容分成多条记录

有一个用逗号分隔各元素的字符串，希望能把这个字符串格式化成一个记录

一、每个元素一行

SELECT B.* FROM (SELECT [value] = CONVERT(XML , '<v>' + REPLACE('1,2,3,长沙,2,3,4,广州,2015-12-15' , ',' , '</v><v>') + '</v>')) A 
CROSS APPLY (SELECT 
[From] = N.v.value('.' , 'varchar(100)') FROM A.[value].nodes('/v') N ( v ) ) B;

二、每个元素一列

SELECT B.* FROM (SELECT [value] = CONVERT(XML , '<v>' + REPLACE('1,2,3,长沙,2,3,4,广州,2015-12-15' , ',' , '</v><v>') + '</v>')) A 
CROSS APPLY (SELECT 
[FromProvince] = A.[value].value('v[1]', 'nvarchar(100)'),
[FromCity] = A.[value].value('v[2]', 'nvarchar(100)'), 
[FromDistrict] = A.[value].value('v[3]', 'nvarchar(100)'),
[From] = A.[value].value('v[4]', 'nvarchar(100)'),
[ToProvince] = A.[value].value('v[5]', 'nvarchar(100)'),
[ToCity] = A.[value].value('v[6]', 'nvarchar(100)'),
[ToDistrict] = A.[value].value('v[7]', 'nvarchar(100)'),
[To] = A.[value].value('v[8]', 'nvarchar(100)'), 
[ToTime] = A.[value].value('v[9]', 'nvarchar(100)')) B;