学习了

路过看一下

坐等大佬

SQL如下，最内层为构造data_source

select dept_type,dept_code,dept_name,
　　　substr(col,1,instr(col,'-')-1) as col,
　　　substr(col,instr(col,'-')+1,length(col)-instr(col,'-')) as val
from (
　　　SELECT distinct dept_type,dept_code,dept_name,
　　　　　　　regexp_substr(';'||'col1-'||col1||';'||'col2'||-col2||';'||'col3'||'-'||col3,'[^;]+',1,level) as col
　　　from (
　　　　　　select 'A' as dept_type,'A1' AS dept_code,'A1_Name' as dept_name,1 as col1,6 as col2,11 as col3 from dual
　　　　　　union all
　　　　　　select 'A' as dept_type,'A2' AS dept_code,'A2_Name' as dept_name,2 as col1,7 as col2,12 as col3 from dual
　　　　　　union all
　　　　　　select 'B' as dept_type,'B1' AS dept_code,'B1_Name' as dept_name,3 as col1,8 as col2,13 as col3 from dual
　　　　　)
　　　connect by level < regexp_count(';'||'col1-'||col1||';'||'col2'||-col2||';'||'col3'||'-'||col3,';') + 1
　　　)
order by 1,2,3,4   

列转行，可以用 unpivot 函数，具体语法可以自己百度下，我这里看出简单示例：

WITH source_tbl AS
(SELECT 'A' AS dept_type
,'A1' AS dept_code
,'A1_Name' AS dept_name
,1 AS col1
,6 AS col2
,11 AS col3
FROM dual 　　　　　　union ALL 　　　　　　select 'A' AS dept_type
,'A2' AS dept_code
,'A2_Name' AS dept_name
,2 AS col1
,7 AS col2
,12 AS col3
FROM dual 　　　　　　union ALL 　　　　　　select 'B' AS dept_type
,'B1' AS dept_code
,'B1_Name' AS dept_name
,3 AS col1
,8 AS col2
,13 AS col3
FROM dual)
SELECT *
FROM source_tbl unpivot(val FOR col IN(col1
,col2
,col3));

select * from source_tbl unpivot (val for col in (col1,col2,col3));