2018年3月1日 星期四

[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)ORACLELISTAGG函數最大長度為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~10002000兩種測試情境,當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串連字串過長


1 則留言: