SQL中FOR XML PATH 语句[可用于分组统计]

FOR XML PATH 语句 :（应该在sql2005及以上版本才能用）

declare @t table(id int,name varchar)
insert into @t(id,name) values(1,'a')
insert into @t(id,name) values(2,'b')

select id,name from @t for xml path

运行结果如下：

<row>
  <id>1</id>
  <name>a</name>
</row>
<row>
  <id>2</id>
  <name>b</name>
</row>

 

select id,name from @t for xml path('node')

运行结果如下：

<node>
  <id>1</id>
  <name>a</name>
</node>
<node>
  <id>2</id>
  <name>b</name>
</node>

下面我要做一个分组统计：

declare @student table(class varchar(10),name varchar(10))
insert into @student(class,name) values('1班','张三')
insert into @student(class,name) values('2班','李四')
insert into @student(class,name) values('1班','王五')
insert into @student(class,name) values('2班','郭佳')
insert into @student(class,name) values('3班','吴亮')

select 
class,
STUFF((select ','+name from @student where class=stu.class for xml path('')),1,1,'') names 
from @student stu
group by class
order by class

运行结果如下：

1班   张三,王五
2班   李四,郭佳
3班   吴亮

同样的效果还可以换一种方式实现：

select class,RIGHT(names,LEN(names)-1) from
(
	select 
	class,
	(select ','+name from @student where class=stu.class for xml path('')) names 
	from @student stu
	group by class
	
) stu
order by class

运行结果如下：

1班   张三,王五
2班   李四,郭佳
3班   吴亮

 

STUFF用法

1、作用

删除指定长度的字符，并在指定的起点处插入另一组字符。

2、语法

STUFF ( character_expression , start , length ,character_expression )

3、示例

以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串

SELECT STUFF('abcdef', 2, 3, 'ijklmn')
GO

下面是结果集

aijklmnef