SQL Server使用for xml path 多行合并成一行，逗号分隔，拆解分析实现原理

我们写sql脚本处理数据的时候 针对部分数据进行group by 分组，分组后需要将部分数据放入分组后的行里面以逗号分隔。

举一个简单例子：

 如上图的数据，需要对学生进行分组，取得学生都参与了哪些学科的考试和 所有总分。

如下图这种数据

目前有两种方案，

1. 在SQL Server 2017版本  已经提供了现成的string_agg函数 使用方式比较简单，但有版本限制，需要注意。

代码如下：

select 
Student
,subjects=STRING_AGG(Subject,',')
,totalScore=SUM(score) 
from 
Score  a
group by Student  

2. 另一种是使用SQL Server的 for xml path 加上分割符的方法取得，这种是比较常见的，兼容性也比较强

select 
Student
,Subjects=stuff((select ','+convert(varchar, [subject])
      from Score b
      where 1=1
     and  b.Student= a.Student
     for xml path('')), 1, 1, '') 
,totalScore=SUM(score) 
from 
Score  a
group by Student  

如上图是具体实现，但该代码是如何实现呢。我们今天具体拆解并分析下实现过程。

我们分步骤进行拆解

1. 先进行一个简单的查询

 select    *
 from Score b
 where 1=1
 and  b.Student=N'李四'

 2. 使用for xml path  将多行数据拆解到一行中

select    *
 from Score b
 where 1=1
  and  b.Student=N'李四'
for xml path 

得到如下图这种数据形式，将数据拆成 xml 放在了一列中

 

 3. 因为我们只需要学科，所以我们只查询学科字段即可

 select [subject]
 from Score b
 where 1=1
  and  b.Student=N'李四'
for xml path

得到如下图数据

 

 4. 接下来是处理其中的xml 节点，有没有办法可以直接去掉对应的 【学科】也就是  <subject></subject>的标签呢

 select convert(varchar, [subject]) 
 from Score b
 where 1=1
  and  b.Student=N'李四'
for xml path 

根据如上sql脚本，

主要是使用了convert函数，将subject 列改为【无列名】 这种方式就会取消 <subject>标签显示，当然其他任意方式都可以。只要是无列名即可。

效果如下图

 

 这就取消掉了xml中 subject显示

5. 接下来是考虑 如何取消 row标签的显示呢？

SQL Server 提供的 for xml path 中，如果 在path() 后 修改为如 path('myrow') 则可以修改 <row>标签显示。

select convert(varchar, [subject]) 
 from Score b
 where 1=1
  and  b.Student=N'李四'
for xml path  ('myrow')

效果图如下

 

 这样就可以修改 row的显示了

6. 那么如何才能不显示row节点呢,其实很简单那，使用 for xml path ('') 即可

具体如下

select convert(varchar, [subject]) 
 from Score b
 where 1=1
  and  b.Student=N'李四'
for xml path  ('')

效果图

 

 至此 就可以实现 将 多行数据放在一列中，接下来是考虑要以逗号分隔。

7. 逗号分隔只需要前面加上逗号字符串即可。

select  ','+ convert(varchar, [subject]) 
from Score b
where 1=1
and  b.Student=N'李四'
for xml path  ('')

效果如下图

 

 8. 由于我们不需要xml这种列名，需要的是自定义列名如何处理呢，其实也很简单

在select前面再加上select 即可。

select    
 (select ','+convert(varchar, [subject])
      from Score b
      where 1=1
      and  b.Student=N'李四'
     for xml path(''))

效果如下图，当然你也可以自定义列名，如 subjects 

 9. 这时候需要考虑去掉最前面的逗号字符串了

使用SQL Server 的函数  stuff 即可，stuff函数这里就不具体讲了。

select subjects=stuff((select ','+convert(varchar, [subject])
   from Score b
   where 1=1
  and  b.Student=N'李四'
  for xml path('')), 1, 1, '')

效果如下图

10 接下来只需要将以上脚本放入 我们主查询的语句中即可。 

放入我们select的子查询脚本中

select 
*
,Subjects=stuff((select ','+convert(varchar, [subject])
      from Score b
      where 1=1
     and  b.Student= a.Student
     for xml path('')), 1, 1, '') 
 
from 
Score  a

效果如下图

 

 11. 这时候直接进行  group by语句就好了

select 
Student
,Subjects=stuff((select ','+convert(varchar, [subject])
      from Score b
      where 1=1
     and  b.Student= a.Student
     for xml path('')), 1, 1, '') 
,totalScore=SUM(score) 
from 
Score  a
group by Student  

效果如下图

结束