[DB] 將 Group by 後的內容濃縮至一列，並以逗號分隔每筆群組資料

不知為何，過去總是很抗拒 SQL 的

group by

指令。不過因為接手的幾個案子需要大量使用到 SQL 的聚合函數 (Aggregation, 如:

Sum, Max, Min

...)，因此就得跟它面對了。而這幾個案子中的需求情境，並不是要加總或計算

Select

出來的最大、最小值，而是將資料

group by

後，將各組的每筆資料以單一符號分隔，並濃縮成一列顯示。這是種比較特別的聚合方法，因此就值得紀錄一下啦。

先看看範例

SELECT * FROM CITY;

 C_ID  C_TYPE  C_NAME
-----  ------  ---------
    1  TW      Nantou
    2  TW      Hsinchu
    3  TW      Chiayi
    4  D       Taipei
    5  D       Taichung
    6  TW      Hualian
    7  TW      Taitung
    8  D       Tainan
    9  D       Kaohsiung

需求：

1. 我想要把以上這些城市依照

   C_TYPE

   欄位進行分組
2. 接著要把各組內容濃縮成一列
3. 每筆資料以半形逗號進行分隔

產生的結果：

CITY_TYPE    CITY_ID      CITY_NAME
----------   ----------   -------------------------------------
        D    4,5,8,9      Taipei,Taichung,Tainan,Kaohsiung
       TW    1,2,3,6,7    Nantou,Hsinchu,Chiayi,Hualian,Taitung

當然也可以透過程式，以迴圈的方式處理。不過有些資料處理是資料庫的強項，能在資料庫處理掉的手續，當然就交給資料庫去代勞囉。究竟這需求要怎麼處理呢？由於首次遇到的案子是使用 Oracle 資料庫，而且還是非常骨董級的 9.2i 版，因此就很勤勞的找出 SQL Server 及 Oracle 兩版本的解法 (Opps.... Oracle 各版本寫法又不太一樣) 詳細細節就不多說了，直接看 Code 吧。

使用 Oracle 9.2i

使用

ROW_NUMBER()

及

SYS_CONNECT_BY_PATH

達成。

--ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i
------------------------------------------------------------------
SELECT C_TYPE as CITY_TYPE,
    LTRIM(MAX(SYS_CONNECT_BY_PATH(C_ID,',')) 
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS CITY_ID,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(C_NAME,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS CITY_NAME
FROM   (SELECT C_TYPE,
               C_ID,
               C_NAME,
               --C_Name
               ROW_NUMBER() OVER (PARTITION BY C_TYPE ORDER BY C_ID) AS curr,
               ROW_NUMBER() OVER (PARTITION BY C_TYPE ORDER BY C_ID) -1 AS prev,
               --C_ID               
               ROW_NUMBER() OVER (PARTITION BY C_TYPE ORDER BY C_ID) -1 AS prev2
        FROM   CITY)
GROUP BY C_TYPE
CONNECT BY prev = PRIOR curr AND C_TYPE = PRIOR C_TYPE
START WITH curr = 1;

使用 Oracle 11g Release 2

使用

LISTAGG

達成。

SELECT C_TYPE AS CITY_TYPE,
 LISTAGG(C_ID, ',') WITHIN GROUP (ORDER BY C_ID) AS CITY_ID,
 LISTAGG(C_NAME, ',') WITHIN GROUP (ORDER BY C_ID) AS CITY_NAME
FROM   CITY
GROUP BY C_TYPE;

參考：String Aggregation Techniques

使用 SQL Server 2008 以上版本

運用

XML PATH

來達成。

SELECT C_TYPE AS CITY_TYPE,
       CITY_ID = STUFF(
                         (SELECT ', ' + CAST(C_ID AS VARCHAR)
                          FROM CITY
                          WHERE C_TYPE = x.C_TYPE
                            FOR XML PATH(''), TYPE).value('.[1]', 'varchar(max)'), 1, 2, ''),
       CITY_NAME = STUFF(
                           (SELECT ', ' + C_NAME
                            FROM CITY
                            WHERE C_TYPE = x.C_TYPE
                              FOR XML PATH(''), TYPE).value('.[1]', 'varchar(max)'), 1, 2, '')
FROM CITY AS x
GROUP BY C_TYPE;

參考：How to use COALESCE with multiple rows and without preceding comma?

使用 SQL Server 2017 以上版本

(2018-12-12 補充)
偶然間在網路文章中發現有人提出使用這個版本才支援的原生函式

STRING_AGG

，微軟真佛心，這語法真是簡單又好用 (不過…目前工作用的系統都還在用 2008R2 咧…新語法雖然好用，但現階段用不到呀)。

SELECT
    C_TYPE AS CITY_TYPE
   ,STRING_AGG(C_ID, ',') AS CITY_ID
   ,STRING_AGG(C_NAME, ',') AS CITY_NAME
FROM CITY po WITH (NOLOCK)
GROUP BY C_TYPE

參考：[小菜一碟] SQL Server FOR XML 退休，欄位資料合併讓 STRING_AGG 來。

Oracle 11gR2 跟 SQL Server 版本的寫法，目前為止只有私底下寫而已，專案上還沒用到。至於 Oracle 9.2i 的版本對於資料量少的資料表來說，真的是很方便。但若是資料量達到上萬筆，效能真的差到不行，最後還是放棄這種寫法。要這麼寫的話，請先衡量資料量再用吧。