匿名用户
5M如下图，有表Table_source，字段如下，怎么用一条SQL查出想要的结果result，列转成行
要求不用union和union all, 不能把前面的3列并成一列
怎么写呢？谢谢
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
评论
有用 1列转行，可以用 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));
评论 有用 2