unstack hierarchical data

I have a table with hierarchical data such as this:

    LEVEL   id_value    parent_id_value     description 
0   1       505         None                Top Hierarchy
1   2       1000        505                 Sub Hierarchy
2   2       300         505                 Other Sub Hierarchy
3   3       0040        300                 Rookie hierarchy
4   3       0042        300                 Bottom level

What I need is a query that will give me this:

0   id_value        3                   2                   1
1         40        Rookie hierarchy    Other Sub Hierarchy Top Hierarchy
2         42        Bottom level        Other Sub Hierarchy Top Hierarchy
3       1000        NULL                Sub Hierarchy       Top Hierarchy

It looks like it should be simple but I'm missing something...

---

I have translated your sample data requirements to an SQL Query. Notice than:

the trip is join table to itself again and again .

Use table alias on each join is mandatory.

You can tune this query to match your general requirements.

To match your data sample, second join is a left join .

Here it is:

select 
  coalesce( l3.id_value,l2.id_value) as id_value ,
  l3.description as "3",
  l2.description as "2",
  l1.description as "1"
from t l1 inner join
     t l2 on l2."LEVEL"=2 and l1.id_value = l2.parent_id_value
          left outer join
     t l3 on l3."LEVEL"=3 and l2.id_value = l3.parent_id_value
where l1.LEVEL = 1

Check it on sqlFiddle

---

This query gives all needed informations:

select id_value, --parent_id_value piv, description, level tlvl, 
    sys_connect_by_path(description, '/') tpath
  from hd where connect_by_isleaf = 1
  start with parent_id_value not in (select id_value from hd)
  connect by parent_id_value = prior id_value

Result

id_value  tpath
--------  ---------------------------------------------------------------
      40  /Top hierarchy/Other sub hierarchy/Rookie hierarchy    
      42  /Top hierarchy/Other sub hierarchy/Bottom level        
    1000  /Top hierarchy/Sub hierarchy

Now if we assume that maximal hierarchy depth is 3 then this query puts subhierarchies in separate columns.

with leaves as (
  select id_value, parent_id_value piv, description, level tlvl, 
      sys_connect_by_path(rpad(description, 20), '/') tpath
    from hd where connect_by_isleaf = 1
    start with parent_id_value not in (select id_value from hd)
    connect by parent_id_value = prior id_value )
select id_value, 
    substr(tpath, 2*20 + 4, 20) l3, 
    substr(tpath, 1*20 + 3, 20) l2,
    substr(tpath, 0*20 + 2, 20) l1
  from leaves

=====================================================================
id_value  L3                    L2                      L1 
      40  Rookie hierarchy      Other sub hierarchy     Top hierarchy       
      42  Bottom level          Other sub hierarchy     Top hierarchy       
    1000                        Sub hierarchy           Top hierarchy       

If description length > 20 change this value to field column length.

This can also be easily done in PL/SQL dynamically, eg by first counting depth, creating table with proper number of columns through execute immediate and putting hierarchies into right columns.

---

Not sure why you need LEVEL column but simple hierarchical query should work. If there is a fixed number of levels just add splitting of CONNECT_BY_PATH to several columns:

-- sample table
CREATE TABLE TT1
 (ID_VALUE          NUMBER,
  PARENT_ID_VALUE   NUMBER,
  DESCRIPTION       VARCHAR2(32));

-- the query itself
 SELECT ID_VALUE,
        REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DESCRIPTION, '/'),  '[^/]+', 1, 3) L3,
        REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DESCRIPTION, '/'),  '[^/]+', 1, 2) L2,
        REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(DESCRIPTION, '/'),  '[^/]+', 1, 1) L1
   FROM TT1
  WHERE CONNECT_BY_ISLEAF = 1
  START WITH PARENT_ID_VALUE IS NULL
CONNECT BY PARENT_ID_VALUE = PRIOR ID_VALUE

链接地址: http://www.djcxy.com/p/93918.html

上一篇: Oracle排序按层次关联

下一篇: 取消分层数据