group by后把没有group的字段拼接成一行，FOR XML PATH('')的使用

FOR XML PATH('')：将查询结果集以XML形式展现。
1、select top 1 opr_id,user_id from dbo.tracking_log FOR XML PATH('oprs')
返回结果xml：

<oprs>
<opr_id>100</opr_id>
<user_id>Natalie813</user_id>
</oprs>
字段名就是xml的节点名，可以 AS 别名。PATH括号中的是根节点名。
2、select '['+opr_id+']' from dbo.tracking_log where user_id = 'Natalie813' FOR XML PATH('')
返回结果：[100][2][4][56]
3、select distinct t1.user_id,
(select t2.opr_id+',' from dbo.tracking_log as t2 where t2.user_id = t1.user_id FOR XML PATH('')) as opr
from dbo.tracking_log as t1
-- where t1.user_id = 'Natalie813'
group by t1.user_id
结果：
列名：user_id         opr
           Natalie813   100,2,4,56,
4、优化步骤3的sql，去掉最后一个逗号

select user_id,LEFT(opr,LEN(opr)-1) from
(
select distinct t1.user_id,
(select t2.opr_id+',' from dbo.tracking_log as t2 where t2.user_id = t1.user_id FOR XML PATH('')) as opr
from dbo.tracking_log as t1
where t1.user_id = 'Natalie813'
group by t1.user_id
) a