【sqlserver】字符串拼接实现（for xml path ，stuff）

create table testbzm (id int,val varchar(10))

insert into testbzm values(1,'AAA'),(2,'BBB'),(3,'CCC')

select * from testbzm 
/*
1   AAA
2   BBB
3   CCC
*/

select * from testbzm for xml path
/*
<row>
  <id>1</id>
  <val>AAA</val>
</row>
<row>
  <id>2</id>
  <val>BBB</val>
</row>
<row>
  <id>3</id>
  <val>CCC</val>
</row>
*/

select * from testbzm for xml path('TEST')
/*
<TEST>
  <id>1</id>
  <val>AAA</val>
</TEST>
<TEST>
  <id>2</id>
  <val>BBB</val>
</TEST>
<TEST>
  <id>3</id>
  <val>CCC</val>
</TEST>
*/

select '['+cast(id as varchar(10))+']','['+val+']' from testbzm for xml path('')
--[1][AAA][2][BBB][3][CCC]

---------------------------------------
--使用场景：字符串拼接，实现ORACLE wmsys.wm_concat(字符) 
--stuff
select stuff('abcdefg',3,2,'111111') --ab111111efg
--stuff(param1, startIndex, length, param2)
--说明：将param1中自startIndex(SQL中都是从1开始，而非0)起，删除length个字符，然后用param2替换删掉的字符。

insert into testbzm values(1,'XXX'),(1,'YYY'),(2,'HHH')

select * from testbzm order by id
/*
1   AAA
1   XXX
1   YYY
2   HHH
2   BBB
3   CCC
*/

--功能实现
select * from (
select a.id,val=
    stuff((select ','+b.val from testbzm b where a.id=b.id for xml path('')),1,1,'')
  from testbzm a
) t
  group by id ,val
order by id