sqlserver stuff 和 for xml path 组合使用

sqlserver 利用 stuff 和 for xml path 把多行数据整合到一行数据

例如：有6个人员信息，要想把姓名这一列查询出来，并显示到一行里面，表结构如下

这里就可以用到 stuff 和 for xml path(’’)函数

-- 根据 user_flag 分组显示
select user_flag,stuff((select ','+user_name from user_table_test where user_flag = a.user_flag for xml path('')),1,1,'') as user_name from user_table_test a group by user_flag

-- 简洁点就直接把所有姓名查询出来，显示到一行上面
select stuff((select ','+user_name from user_table_test for xml path('')),1,1,'') 

执行结果：

---

stuff 用法

STUFF 函数将字符串插入到另一个字符串中。 它从第一个字符串的开始位置删除指定长度的字符；然后将第二个字符串插入到第一个字符串的开始位置。

语法：
STUFF ( 字符串 , 开始位置 , 结束位置 ,替换的字符串 )

举个例子：

-- 把从1开始的位置到1结束的位置给删除掉，然后替换成另一个字符串
select stuff('ABCD',1,1,'E'); --结果：EBCD
-- 也可以替换空值
select stuff('ABCD',1,2,''); --结果：CD

执行结果如下：

---

for xml path 用法

for xml path 将查询出来的数据组装成xml的形式，默认根节点为row

select * from user_table_test for xml path

结果

<row>
	<user_id>1</user_id>
	<user_name>张三</user_name>
	<user_flag>1</user_flag>
</row>
<row>
	<user_id>2</user_id>
	<user_name>李四</user_name>
	<user_flag>1</user_flag>
</row>
<row>
	<user_id>3</user_id>
	<user_name>王五</user_name>
	<user_flag>1</user_flag>
</row>
<row>
	<user_id>4</user_id>
	<user_name>Jack</user_name>
	<user_flag>0</user_flag>
</row>
<row>
	<user_id>5</user_id>
	<user_name>Lucy</user_name>
	<user_flag>0</user_flag>
</row>
<row>
	<user_id>6</user_id>
	<user_name>Tom</user_name>
	<user_flag>0</user_flag>
</row>

path也可以指定节点名称，例如

select * from user_table_test where user_id in (1,2) for xml path('userInfo')

结果

<userInfo>
	<user_id>1</user_id>
	<user_name>张三</user_name>
	<user_flag>1</user_flag>
</userInfo>
<userInfo>
	<user_id>2</user_id>
	<user_name>李四</user_name>
	<user_flag>1</user_flag>
</userInfo>

path 也可以取消节点，换成path(’’) 即可

select * from user_table_test where user_id in (1,2) for xml path('')

结果

<user_id>1</user_id>
<user_name>张三</user_name>
<user_flag>1</user_flag>
<user_id>2</user_id>
<user_name>李四</user_name>
<user_flag>1</user_flag>

可以指定显示哪些列

select  user_name from user_table_test where user_id in (1,2) for xml path

结果

<row>
	<user_name>张三</user_name>
</row>
<row>
	<user_name>李四</user_name>
</row>

对于列的节点也是可以改变的，这就要用给列起别名的关键字AS

select  user_name as name from user_table_test where user_id in (1,2) for xml path

结果：

<row>
	<name>张三</name>
</row>
<row>
	<name>李四</name>
</row>

也可以自定义输出的格式

select '我是'+ user_name as info from user_table_test where user_id in (1,2) for xml path
-- 结果 <row><info>我是张三</info></row><row><info>我是李四</info></row>

取消根节点，取消列节点

select ','+ user_name  from user_table_test where user_id in (1,2) for xml path('')

这时候在结合stuff 函数，去掉一个逗号”，“

-- 把第一个字符串替换成'' 
select stuff( (select ','+ user_name from user_table_test where user_id in (1,2) for xml path('')) ,1,1,'')

这就实现了把多行数据显示到一行数据

---

小知识点

 SELECT datalength(rtrim('字符串')) 

UTF-8 编码汉字占用2-4个字节，GBK编码汉字占用2个字节

---