for xml path +X ——字符串组合神器

之前讲过了根据特定字符把字符串拆分成一列的方法 apply用法（2）——字符串拆分转列

今天再介绍一个“逆运算”，将组内若干列字符串按照特定字符组合到一个字段上。本例中消费者 client 就是组成员，每个client 的组内成员为 gift 和 buydate,要实现的效果是组成员占一行，组内成员特定字符组合到一个字段内，可以大幅压缩数据的行数。

--消费者礼品明细表 TT
client    gift    buydate
1000    礼品A    2020-08-01
1000    礼品B    2020-08-02 
1000    礼品C    2020-08-04 
1200    礼品A    2020-08-02 
1200    礼品D    2020-08-05 
1500    礼品C    2020-08-03 
...     ...

老板说每个消费者的每个产品以及购买时间都要占用一行记录，记录多了看起来很不方便，帮我把产品和时间合并成流水整理到一起吧，这样一个消费者占一行记录，我想看哪个消费者的只要在一行看就好了。

client    text
1000    2020-08-01 礼品A,2020-08-02 礼品B,2020-08-04 礼品C
1200    2020-08-02 礼品A,2020-08-05 礼品D
1500    2020-08-03 礼品C
...     ...

下面介绍 for xml path()

for xml path () 是将查询结果转成xml 格式 

--用法
select * from TT for xml path('')
结果
<client>1000</client>
<gift>礼品A</gift>
<buydate>2020-08-01T00:00:00</buydate>
<client>1000</client>
<gift>礼品B</gift>
<buydate>2020-08-02T00:00:00</buydate>
<client>1000</client>
<gift>礼品C</gift>
<buydate>2020-08-04T00:00:00</buydate>
<client>1200</client>
<gift>礼品A</gift>
<buydate>2020-08-02T00:00:00</buydate>
<client>1200</client>
<gift>礼品D</gift>
<buydate>2020-08-05T00:00:00</buydate>
<client>1500</client>
<gift>礼品C</gift>
<buydate>2020-08-03T00:00:00</buydate>

--path('') 中的 ''可以替换成任意字符作为xml的节点，这里只需要用''就够了

--如果我们在 查询时加上分隔符
select ','+convert(varchar(20),buydate,120)+' '+gift from TT where client=1000 for xml path('')
--输出结果为
,2020-08-01 00:00:00 礼品A,2020-08-02 00:00:00 礼品B,2020-08-04 00:00:00 礼品C

现在发现我们想解决的问题 for xml path 已经解决了99%了，剩下的只需要把第一个分隔符 ","替换掉就完美解决了。这个分隔符可以放在第一位，也可以放在末位。

根据位置不同有不同组合，下面仅提供参考。

--分隔符放在首位
--注1：这里用distinct 和 group by 效果一样，都是为了避免重复
--注2：实际业务中可能出现组合的字符串不完整，是因为 sql server 将第一个字符串的长度作为整列的标准长度，超过这个长度的将被截断，如何解决呢，只需要人为规定一下字段长度就好了,在本例 cast(','+convert(varchar(20),buydate,120)+' '+gift,as varchar(200))

--for xml path + stuff 
select distinct client,stuff((select ','+convert(varchar(20),buydate,120)+' '+gift from TT a where a.client=TT.client for xml path('')),1,1,'')text from TT 

--for xml path + substring 
select distinct client,substring((select ','+convert(varchar(20),buydate,120)+' '+gift from TT a where a.client=TT.client for xml path('')),2,LEN((select ','+convert(varchar(20),buydate,120)+' '+gift from TT a where a.client=TT.client for xml path(''))))text from TT 

--for xml path + right 
select distinct client,right((select ','+convert(varchar(20),buydate,120)+' '+gift from TT a where a.client=TT.client for xml path('')),LEN((select ','+convert(varchar(20),buydate,120)+' '+gift from TT a where a.client=TT.client for xml path('')))-1)text from TT 

--结果都是 
client text 
1000 2020-08-01 00:00:00 礼品A,2020-08-02 00:00:00 礼品B,2020-08-04 00:00:00 礼品C 
1200 2020-08-02 00:00:00 礼品A,2020-08-05 00:00:00 礼品D 
1500 2020-08-03 00:00:00 礼品C

 

--分隔符放在末位

--for xml path + stuff
select distinct client,stuff((select convert(varchar(20),buydate,120)+' '+gift+',' from TT a where a.client=TT.client for xml path('')),LEN((select convert(varchar(20),buydate,120)+' '+gift+',' from TT a where a.client=TT.client for xml path(''))),1,'')text from TT

--for xml path + substring
select distinct client,substring((select convert(varchar(20),buydate,120)+' '+gift+',' from TT a where a.client=TT.client for xml path('')),1,LEN((select ','+convert(varchar(20),buydate,120)+' '+gift from TT a where a.client=TT.client for xml path('')))-1)text from TT

--for xml path + left
select distinct client,left((select convert(varchar(20),buydate,120)+' '+gift+',' from TT a where a.client=TT.client for xml path('')),LEN((select ','+convert(varchar(20),buydate,120)+' '+gift from TT a where a.client=TT.client for xml path('')))-1)text from TT

--结果都是
client    text
1000    2020-08-01 00:00:00 礼品A,2020-08-02 00:00:00 礼品B,2020-08-04 00:00:00 礼品C
1200    2020-08-02 00:00:00 礼品A,2020-08-05 00:00:00 礼品D
1500    2020-08-03 00:00:00 礼品C

stuff ,substring ,left 是基础函数不做展开，可以发现最好的写法是 分隔符放在首位且 +stuff 的组合  写法最简洁。推荐这种写法。