SQL Server使用FOR XML PATH实现字符串拼接功能

• 将结果集转换为xml格式

SELECT ru.userid FROM table1 ru WHERE ru.roleid IN ('r001', 'r003') FOR XML PATH('');

-- 结果
<userid>u001</userid><userid>u001</userid><userid>u008</userid><userid>u012</userid><userid>u013</userid><userid>u053</userid><userid>u056</userid><userid>u058</userid><userid>u078</userid><userid>u091</userid><userid>u099</userid><userid>u103</userid><userid>u103</userid><userid>u104</userid><userid>u104</userid>

• xml格式改为字符串

SELECT ',' + ru.userid FROM table1 ru WHERE ru.roleid IN ('r001', 'r003') FOR XML PATH('');

-- 结果
,u001,u001,u008,u012,Yu013,u053,u056,u058,u078,u091,u099,u103,u103,u104,u104

• 去重

SELECT distinct ',' + ru.userid FROM table1 ru WHERE ru.roleid IN ('r001', 'r003') FOR XML PATH('');

-- 结果
,u001,u008,u012,Yu013,u053,u056,u058,u078,u091,u099,u103,u104

• 去掉头部的逗号

SELECT stuff((SELECT distinct ',' + ru.userid FROM table1 ru WHERE ru.roleid IN ('r001', 'r003') FOR XML PATH('')), 1, 1, '');

-- 结果
u001,u008,u012,Yu013,u053,u056,u058,u078,u091,u099,u103,u104