[MSSQL] 字串彙總(CSV String)

許多實務應用時常需將群組特定字串組合成格式化字串(如CSV字串)，如下表列出部門員工列表。

Dept	Emp_List
HR	Jane
IT	Andy,Kevin
MA	James,May

許多資料庫較新版本已提供字串彙總函數，如MySQLGROUP_CONCAT指令、ORACLE 11g之LISTAGG或MSSQL 2017之STRING_AGG函數。

資料庫	指令
MySQL	GROUP_CONCAT
ORACLE 11g	LISTAGG
MSSQL 2017	STRING_AGG

MSSQL2017以下版本，可採用ML PATH進行字串組合，由於指令稍稍繁雜且使用上並不簡便，但為內建功能而常被應用，但強烈建議以CLR建立User-Defined Aggregate Functions。

	SQL
方法1	SELECT DISTINCT E.Dept      , STUFF(                  (                  SELECT ',' +  A.EmpName                FROM #Emp A                WHERE E.Dept = A.Dept                  ORDER BY A.EmpName                FOR XML PATH('')                  )                  , 1, 1, ''      ) AS Emp_List           FROM #Emp E
方法2	SELECT DISTINCT E.Dept      , SUBSTRING(EmpList, 1, LEN(EmpList)-1) EmpList FROM  #Emp E       CROSS APPLY          (          SELECT COALESCE(A.EmpName, '')  + ','          FROM  #Emp A          WHERE E.Dept = A.Dept           ORDER BY A.EmpName          FOR XML PATH('')          ) X (EmpList) *提醒: 此語法在2014版本中建立為VIEW時，發生1033錯誤，方法1則可。2016卻正常。

建立資料: 

SELECT * INTO #Emp

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

        ,('IT', 'Andy')

        ,('IT', 'Kevin')

        ,('MA', 'May')

        ,('MA', 'James')

        ) AS Emp(Dept, EmpName)

[MSSQL] JSON資料交換

JavaScript Object Notation (JSON) 為一種以文字為基礎的輕量級資料交換語言，常應用於網站資料呈現、傳輸等，將資料從伺服器送至用戶端，並可由網頁直接呈現。MSSQL2016版本內建json功能，可簡化前端應用程式於資料編組json字串，或者拆解傳輸（交換）資料等應用。

步驟	SQL
產生Json	SELECT * FROM (VALUES('2330', 240.50, '20180116')           , ('0050',  85.00, '20180116')           , ('3008',   4000, '20180116')       ) PRI(StkId, Cls_Pri, DT) FOR JSON AUTO
匯入Json	DECLARE @json varchar(max) SET @json='[{"StkId":"2330","Cls_Pri":240.50,"DT":"20180116"} ,{"StkId":"0050","Cls_Pri":85.00,"DT":"20180116"} ,{"StkId":"3008","Cls_Pri":4000.00,"DT":"20180116"}]' SELECT * FROM OPENJSON (@json) WITH   (        StkId   varchar(10),       Cls_Pri NUMERIC(10,4),       DT      datetime   )

參考資料:

https://docs.microsoft.com/zh-tw/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server

https://docs.microsoft.com/zh-tw/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server

CSV String (欄位值) 2 Row (Table)

純粹CSV格式字串(如: A,B,C)轉換現行作法非常簡便，但若為資料表中資料，則處理上則稍具難度，查詢資料表中其他欄位資料值需配合CSV展開成多筆，MSSQL 2016版本起提供STRING_SPLIT函數可直接呼叫使用，其他版本或ORACLE則可利用XML進行展開，範例如下:

版本	SQL
MSSQL 2005	SELECT Id      , VAL      , ROW_NUMBER() OVER (ORDER BY GETDATE()) SEQ      , Split.Col.value('.', 'VARCHAR(100)') AS X FROM     (     SELECT D.Id, D.VAL           , CAST ('<S>'                    + REPLACE(D.VAL, ',', '</S><S>')                    + '</S>' AS XML        ) AS Val_List     FROM (VALUES (1, 'B,A')                      , (2, 'B,C')) AS D(Id, VAL)      ) D CROSS APPLY Val_List.nodes ('/S') AS Split(Col) 註: VALUES為2008指令
MSSQL 2016	SELECT Id, VAL       , value     FROM (VALUES(1, 'B,A')           , (2, 'B,C'))AS D(Id, VAL)      CROSS APPLY STRING_SPLIT(VAL, ',')
ORACLE 10g	WITH TT AS  ( SELECT 1 Id, 'B,A' VAL FROM DUAL UNION ALL SELECT 2 Id, 'B,C' VAL FROM DUAL ) SELECT Id      , VAL      , EXTRACT(VALUE(I), '//row/text()').getstringval () AS X FROM     (     SELECT Id, VAL            , XMLTYPE('<rows><row>'                         || REPLACE (Val, ',', '</row><row>')                         || '</row></rows>'                    ) AS XMLString     FROM TT     ) D, TABLE (XMLSEQUENCE(EXTRACT (D.XMLString, '/rows/row'))) I;

CSV String 2 Row(Table)

純粹CSV格式字串(如: A,B,C)，資料可能為使用者輸入或傳入參數等，現已有許多函數或基本作法處理上非常簡便，ORACLE可直接使用DBMS_DEBUG_VC2COLL或使用者自建Type等方法進行轉換，MSSQL於2016版本起提供STRING_SPLIT函數可直接呼叫使用。

版本	SQL
MSSQL 2005	SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) SEQ      , Split.Col.value('.', 'VARCHAR(100)') AS X FROM     (     SELECT CAST ('<S>'                    + REPLACE('A,B,C', ',', '</S><S>')                     + '</S>' AS XML                     ) AS Val_List     ) AS A CROSS APPLY Val_List.nodes ('//S') AS Split(Col)
MSSQL 2016	SELECT value --欄位名稱為value FROM STRING_SPLIT('A,B,C', ',')
ORACLE 10g	SELECT column_value FROM TABLE(SYS.DBMS_DEBUG_VC2COLL( 'A','B','C' ) ); SELECT column_value FROM TABLE( SYS.DBMS_DEBUG_VC2COLL( 1, 2, 3 )); 9i版本可使用自建TYPE即可 CREATE OR REPLACE TYPE SPLIT_TBL AS TABLE OF VARCHAR2(32767) ; SELECT column_value FROM TABLE(SPLIT_TBL( 'A','B','C' ) );