CTE應用-將多筆查詢結果合併成逗號分隔字串

一對多關聯是常見的資料庫應用情境,有時我會遇到將多筆關聯資料特定欄立合併成逗號分隔字串的需求。聽起來有點抽象,用個實例說明。

假設有兩個資料表,應用系統清單及負責該系統的工程師名字,用以下指令建立模擬資料:

CREATE TABLE AppSystem (
  Name VARCHAR(16),
  CodeName VARCHAR(16)
)
INSERT INTO AppSystem VALUES ('HR', 'Mars');
INSERT INTO AppSystem VALUES ('ERP', 'Jupiter');
INSERT INTO AppSystem VALUES ('POS', 'Venus');
CREATE Table AppSupport (
   AppName VARCHAR(16),
   Engineer VARCHAR(16)
)
INSERT INTO AppSupport VALUES ('HR', 'Jeffrey');
INSERT INTO AppSupport VALUES ('ERP', 'Jeffrey');
INSERT INTO AppSupport VALUES ('POS', 'Jeffrey');
INSERT INTO AppSupport VALUES ('ERP', 'Darkthread');
INSERT INTO AppSupport VALUES ('POS', 'Darkthread');
INSERT INTO AppSupport VALUES ('HR', 'SecrectSound');
INSERT INTO AppSupport VALUES ('ERP', 'MouthCannon');

如下圖所示,資料顯示有HR、ERP、POS三個系統,而工程師共四位,每個人各負責1到3個系統。

用簡單的JOIN查詢就能列出每個系統負責的工程師,但有個缺點,每個工程師配一個系統會出現一次,故系統資料會重複,例如:HR、POS各出現兩次,ERP出現三次。

若目的在產生報表,理想的呈現方式是將同一系統的工程師名字合併成以逗號分隔的字串,像這樣:

要轉換成上述格式,我試過幾種做法:

  1. 建Temp Table跑Cursor統整資料
    我用T-SQL寫Cursor的功力很鳥,有馬拉松跑者被抓去游泳的fu,而建Temp Table跑Cursor迴圈的程序感覺有點繁瑣沒效率。
  2. 將JOIN結果拉回.NET端整理
    寫C#彙整資料這類雕蟲小技是我的強項,幾乎是信手拈來,開發跟執行效率都不會太差。但拉到.NET端處理有個缺點,JOIN完的重複資料需透過網路傳輸傳到網站伺服器後才能加工,若主資料的欄位又多又長,同一筆重覆次數又高,浪費的頻寬很可觀。

爬文找到更多做法,包含FOR XML PATH、自訂函式、SQLCLR函式、CTE… 等。之前體驗過CTE(Common Table Expresion)的特異功能,讓我頗為驚喜,當發現CTE也可解決這類需求,便決定動手試試。

CodeProject上有一篇好文章,照方煎藥,這個案例的合併欄位CTE寫法如下:

;WITH SupportCTE (RowNum, Name, CodeName, Engineer,Engineers) AS
(
    SELECT 1, A.Name, A.CodeName, MIN(B.Engineer), 
           CAST(MIN(B.Engineer) AS VARCHAR(MAX)) AS Engineers
    FROM AppSystem A JOIN AppSupport B ON A.Name = B.AppName
    GROUP BY A.Name, A.CodeName
 
    UNION ALL
 
    SELECT A.RowNum + 1, A.Name, A.CodeName, B.Engineer, 
           CAST(A.Engineers + ', ' + B.Engineer AS VARCHAR(MAX)) As Supports
    FROM SupportCTE A JOIN AppSupport B 
    ON A.Name = B.AppName AND B.Engineer > A.Engineer 
)
 
SELECT * FROM SupportCTE

上次一樣,我們要善用 CTE 主查詢 UNION ALL 遞迴查詢的原理解決問題。程式有幾個重點,第一段為主查詢,JOIN AppSystem及AppSupport兩個資料表,除了Name及CodeName、Engineer外,又多加RowNum及Engineers兩個欄位。Engineers存放工程師名字串接結果,RowNum統計Engineers包含的工程師資料筆數。一個系統可能有多位工程師,Engineer要用GROUP BY及MIN()找出名字排序最前者(稍後遞迴時要靠它排除處理過的項目,防止陷入無窮遞迴,這個環節很重要)。UNION ALL接的第二段查詢將被遞迴呼叫(故一定要設計跳出遞迴的邏輯),原理是利用現有結果反覆JOIN AppSupport找出同一系統的其他工程師,將其串接於Engineers欄位後方,同時將RowNum+1,Engineer則換成本次串接的工程師名稱。JOIN條件限定工程師名字排序要在既有資料之後,可排除已串接過的項目,避免無窮迴圈。

直接SELECT SupportCTE可以看到遞迴查詢的結果,比較能體會其運作原理。

如上圖所示,每個系統都有多筆資料,Engineers分別由1-3位工程師名字串接而成,RowNum即為串接資料的個數。如此,我們只需找出每個系統RowNum最大的一筆,即為最終結果。這應該難不倒冰雪聰明的你,用以下寫法就可輕鬆搞定:

SELECT A.Name, A.CodeName, A.Engineers FROM SupportCTE A
JOIN (SELECT Name, Max(RowNum) AS MaxRowNum FROM SupportCTE GROUP BY Name) B
ON A.Name = B.Name AND A.RowNum = B.MaxRowNum

補充:針對找出RowNum最大的一筆,路人乙提供一個更棒的解法,不用JOIN就可以搞定,特此感謝!

SELECT Name,CodeName,Engineers
FROM (
  SELECT *,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY RowNum Desc) AS Pos
  FROM SupportCTE
) T
WHERE Pos = 1 
ORDER BY Name

薑!薑!薑!薑~

實做一趟的感想:用CTE實現多筆資料串接需要動用遞迴,設計起來要費點腦筋,有點呼應那句「遞迴只應天上有,凡人應當用迴圈」的俗諺,但只要悟透原理,也不到難以駕御的程度,而且試出來的那一刻,還有種自己通過智力測驗的成就感(笑),我想我會開始在專案上試用這種寫法

2016-01-21更新:經過實測,若只是要合併欄位,FOR XML PATH比CTE簡便有效率

歡迎推文分享:
Published 19 January 2016 06:36 AM 由 Jeffrey
Filed under:
Views: 15,616



意見

# 路人乙 said on 18 January, 2016 09:15 PM

建議用 ROW_NUMBER() 可以減少 joining

select Name,CodeName,Engineers

from(

SELECT *,pos=ROW_NUMBER() over(partition by Name order by RowNum Desc)

FROM SupportCTE

) t

where pos=1

order by Name

# Jeffrey said on 18 January, 2016 10:18 PM

to 路人乙,很棒的建議,感謝!已納入本文

# ChoeChin said on 18 January, 2016 11:36 PM

To 黑暗大大:

小弟以前做過類似了~是用下面方式解決~

Select distinct d.Name ,d.CodeName,

(Select c.Engineer +','

From (select a.Name,a.CodeName,b.Engineer from AppSystem a

Left Join

AppSupport b On a.Name=b.AppName) c

where c.Name=d.Name for xml path('')

) as Engineer

From

(select a.Name,a.CodeName,b.Engineer from AppSystem a

Left Join AppSupport b On a.Name=b.AppName) d

# Jeffrey said on 19 January, 2016 12:31 AM

to ChoeChin, 謝謝分享。依我直覺,感覺FOR XML PATH解法可能存在效能疑慮(子查詢加上XML轉換)所以對CTE解法較感興趣,但純粹只是猜想。這個議題挺有趣,改天可以實測看看。

# sleepycat said on 19 January, 2016 02:08 AM

黑大之前就寫過一篇 "SQL 2005 T-SQL Enhancement: Common Table Expression"

(blog.darkthread.net/post-2007-05-23-sql-2005-t-sql-enhancement-common-table-expression.aspx)

當初就是靠這篇解決了一些問題呢~

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<January 2016>
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication