【达梦系列】列转行与用逗号分割一行转多行

我的炸串拌饼店

已于 2024-06-07 15:46:19 修改

阅读量907

点赞数 11

分类专栏：达梦数据库文章标签：数据库数据库开发

于 2024-05-11 14:26:17 首次发布

本文链接：https://blog.csdn.net/weixin_46424784/article/details/138714123

版权

达梦数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

一、数据准备

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