一、数据准备

CREATE TABLE a_test
(
"id" VARCHAR(36) NOT NULL,
"Src1" VARCHAR(1000),
"Src2" VARCHAR(1000)
)

insert into a_test("id", Src1, "Src2")
       VALUES(
       '55DAA486-43FE-4CFA-9009-3AAFFE3E13F1',
       '2c3fdc1a-c3a0-4c19-bba2-d9c92ba5bd3b.jpg,0dc38b43-df9d-4d98-a3c7-7d815a0cbb3f.jpg,e08c320f-fce2-4a7e-b56f-bbbd57e33e02.jpg,7a9373fa-02b5-4bdb-86c2-e93314d1df24.jpg',
       '/UpStore/InspectRpt/f4acbf6d-e00b-4c08-934b-1a5644613a0e.pdf');

二、sql语句

select id, AreaCode, state, FileTypes,
      column_value AS FileName
       from  (
  SELECT id, FileTypes,
         XMLPARSE(DOCUMENT '<root><v>' || REPLACE(FileNames, ',', '</v><v>') || '</v></root>') AS file_xml
  FROM (
     SELECT *from  a_test
        UNPIVOT   
        (  
            FileNames FOR FileTypes IN  
            (Src1,Src2)  
        ) P  	
  ) as a
  ORDER BY id asc
) as b
OUTER APPLY  XMLTable('/root/v' PASSING file_xml COLUMNS  column_value VARCHAR  PATH '.' ) AS x
where column_value is not NULL

三 知识点链接

1、PIVOT 和 UNPIVOT 子句

2、XMLPARSE ctrl+f 搜索 XMLPARSE

3、OUTER APPLY

4、XMLTable ctrl+f 搜索XMLTable

四、结果