2018年1月16日 星期二

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)
: VALUES2008指令
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;



沒有留言:

張貼留言