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;