[MSSQL] STRING_AGG字串彙總分析函數

MSSQL 2017提供STRING_AGG字串彙總函數（群組字連結函數），類似MySQL所提供的GROUP_CONCAT函數或ORACLE 11gR2 LISTAGG函數；與《CSV String 2 Row(Table)》文章所介紹2016版本所提供STRING_SPLIT字串拆分函數功能，兩者為反向應用功能。

MSSQL 2017

STRING_AGG(expr , 'delimiter') [<order_by_clause>] <order_clause> ::=      WITHIN GROUP ( ORDER BY <order_by_expr>)

expr：用於字串連結之欄位（Column）或運算(expression)（必要參數）。

delimiter：字串連結之分隔符號（必要參數）。

order_by_clause：字串連結之順序關鍵字組（選擇參數）。

ORACLE 11g R2

LISTAGG(expr [, 'delimiter'])   WITHIN GROUP (<order_by_clause>) [OVER partition_by_clause]

expr：用於字串連結之欄位（Column）或運算(expression)（必要參數）。

delimiter：字串連結之分隔符號（選擇參數）。

order_by_clause：字串連結之順序（必要參數）。

    

	MSSQL 2017	ORACLE 11g R2
函數名稱	STRING_AGG	LISTAGG
分隔符號參數	必要參數	選擇參數。預設逗號
WITHIN GROUP關鍵字	選擇參數	必要參數
ORDER BY子句	選擇參數	必要參數
輸出型態	*與第一個參數相同 通常為8000 bytes	varchar2 4000 bytes
GROUP BY子句（彙總）	需搭配使用	需搭配使用
DISTINCT關鍵字	不支援	不支援
OVER子句	不支援	支援

MSSQL輸出型態通常由第一個型態所決定，但通常為varchar即最大長度為8000 bytes，但可先將第一個參數轉型為其他型態如varchar(max)；ORACLE的LISTAGG函數最大長度為4000 bytes，但可改用xmlagg函數，則改變可輸出CLOB型態，可參考《[ORACLE] LISTAGG函數之應用》文章所述。

目前暫無MSSQL2017環境，因此使用SQL Fiddle所提供之測試環境進行測試，雖測試資料是由VALUES直接產生，但右測DDL區仍需建立供網站系統檢核的資料表，測試結果如下 :

由測試結果，兩種語法均可得到所需。請注意此為字串彙總函數，如省略GROUP BY字句，則將發生錯誤。

SELECT Dept

  , STRING_AGG(EmpName, ',') WITHIN GROUP (ORDER BY EmpName) "Test-1"

  , STRING_AGG(EmpName, ',')  "Test-2"

  --, STRING_AGG(EmpName, ',') WITHIN GROUP (ORDER BY EmpName) OVER() "Test-3"

FROM (VALUES('HR', 'Jane')

       -- , ('IT', 'Kevin')

        , ('MA', 'May')

        , ('MA', 'James')

        , ('IT', 'Kevin')

        , ('IT', 'Andy')

      ) AS Emp(Dept, EmpName)

GROUP BY Dept;

以下將進行輸出型態測試，將以master..spt_values系統資料表產生0001~1000及2000兩種測試情境，當2000時，由於長度已超過MSSQL字元型態上限8000bytes將發生exceeded the limit of 8000 bytes錯誤，如先前文章所探討，MSSQL輸出型態通常是由第一個輸入參數所決定，將其轉型為VARCHAR(MAX)即可。如下：

SELECT STRING_AGG(RIGHT(CONCAT('0000', number) , 4), ',')

      , STRING_AGG(CAST(RIGHT(CONCAT('0000', number) , 4) AS VARCHAR(MAX)), ',')

FROM master..spt_values

WHERE 1=1

  AND type ='p'

  AND number <=1000

  --AND number <=2000

可自行測試對應之ORACLE語法，1000組時可順利完成(4000 byte)，但增加為1001時，則發生ORA-01489錯誤。

SELECT LISTAGG(LPAD(LEVEL, 4, '0'), '') WITHIN GROUP(ORDER BY 1)

FROM DUAL
CONNECT BY LEVEL <=1000 --如1001則長度為4004(超過4000)，發生ORA-01489串連字串過長