2011年5月3日 星期二

使用 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)

3 則留言:

  1. 補充:

    如果要串多個欄位時,記得加上 isnull( ) ,避免遇到 null 欄位而得到非預期的結果。

    ex:
    select isnull(area1,'')+','+isnull(area2,'')+',' from #tmp sun for xml path('')

    回覆刪除