Oracle不用Group by 的列转行(挑战高手)
oracle 树形结构数据 ,字段全是字符串 ,如何列转行成一条数据
id pid(父节点) name level(结构标志)
1 2 a 1
2 3 b 2
3 4 c 4
a b q1 1
b c e1 3
c d h1 5
转化成两条数据
id level1 level2 level3 level4 level5
1 a b c
a q1 e1 h1
高手啊!!
------解决方案--------------------
这个比较难吧,关键level还不是固定的
------解决方案--------------------
这个太难了,哈哈,期待大神的答案
------解决方案--------------------
这样可以??
with t as
(select '1' id, '2' pid, 'a' name, '1' lev
from dual
union all
select '2', '3', 'b', '2'
from dual
union all
select '3', '4', 'c', '4'
from dual
union all
select 'a', 'b', 'q1', '1'
from dual
union all
select 'b', 'c', 'e1', '3'
from dual
union all
select 'c', 'd', 'h1', '5' from dual),
t1 as
(select connect_by_root(id) id, name, lev
from t
start with lev = '1'
connect by id = prior pid)
select *
from t1 pivot(max(name) for lev in(1 level1,
2 level2,
3 level3,
4 level4,
5 level5));
------解决方案--------------------
引用:Quote: 引用:这样可以??
with t as
(select '1' id, '2' pid, 'a' name, '1' lev
from dual
union all
select '2', '3', 'b', '2'
from dual
union all
select '3', '4', 'c', '4'
from dual
union all
select 'a', 'b', 'q1', '1'
from dual
union all
select 'b', 'c', 'e1', '3'
from dual
union all
select 'c', 'd', 'h1', '5' from dual),
t1 as
(select connect_by_root(id) id, name, lev
from t
start with lev = '1'
connect by id = prior pid)
select *
from t1 pivot(max(name) for lev in(1 level1,
2 level2,
3 level3,