使用 MSSQL 打造仿 MySQL 的group_concat 函数的山寨版

在 MySQL 有提供一個函數：group_concat，他可以讓你將資料根據某個欄位進行分組，並將分組後的結果，以逗號來分隔多筆資料。譬如說，可以將下列的資料：

轉變成：

這時候，可以善用 SQL 2005 後針對 xml 所提供相關函式來協助。透過一步一步的觀察，可以找到不錯的解決方式。

首先，先建立好我的測試資料：

create table #tmp (id int identity(1,1),empname varchar(10),area nvarchar(100))

insert into #tmp(empname,area) values ('paladin','新竹縣')
insert into #tmp(empname,area) values ('paladin','新竹市')
insert into #tmp(empname,area) values ('paladin','高雄市')
insert into #tmp(empname,area) values ('ken','嘉義市')
insert into #tmp(empname,area) values ('ken','雲林縣')
insert into #tmp(empname,area) values ('hugo','台南市')
insert into #tmp(empname,area) values ('hugo','台北市')

接著使用  「for xml path」來指定符合我們所要的 xml 結果：

select area  from #tmp sun  for xml path('')

產生結果是：

目前看起來，還需要進一步刪掉左右兩邊的 Tag。這問題，可以透過我們設定查詢欄位的連結字串來消除，可以只是簡單的在查詢欄位加上個逗號來完成。

select area+','  from #tmp sun  for xml path('')

xml 的結果整理好後，接著透過父子關係來處理。

select parent_tb.empname,
(select area+','  from #tmp sun 
  where sun.empname=parent_tb.empname 
   for xml path('')) area 
from #tmp parent_tb

依目前的資料，只需再進行 group 就可以了。

select parent_tb.empname,
(select area+','  from #tmp sun 
  where sun.empname=parent_tb.empname 
   for xml path('')) area 
from #tmp parent_tb 
group by empname

經過這幾個步驟的測試後，終於完成山寨版的 group_concat 功能了。

如果您想要得到字串結果不希望後面留下逗號，可以透過 stuff 函數來完成，調整一下原先的程式變成：

select parent_tb.empname,
 stuff((select','+area  from #tmp sun 
  where sun.empname=parent_tb.empname 
   for xml path('')),1,1,'') area 
from #tmp parent_tb 
group by empname

Ref
01.MySQL中group_concat函数
02.sql 2008如何实现mysql中group_concat？
03.What's New in FOR XML in Microsoft SQL Server 2005
04.STUFF (Transact-SQL)