MYBLOG

1、将SQL查询出的某一列数据，拼接成一行数据，并以逗号为分隔

例如将下图查询到的数据

     转换为

其SQL语法：

   SELECT DISTINCT STUFF(
  (SELECT ',' +CAST(NUMBER AS NVARCHAR(255)) FROM [MASTER].[DBO].[SPT_VALUES]  FOR XML PATH('')),1, 1, '') AS A
FROM [MASTER].[DBO].[SPT_VALUES]

2、反之将上边的数据逆向转换

SELECT id FROM (SELECT [value] = CONVERT(XML , '<v>' + REPLACE('111111,222222,33333,44444,55555', ',' , '</v><v>')+ '</v>')
 ) A OUTER APPLY ( SELECT id = N.v.value('.' , 'varchar(100)')FROM A.[value].nodes('/v') N (v)) B