Oracle Tree Structure Hierarchy Data Display in Single Row Different Column
I have a hierarchical table named Employee_Hierarchy in oracle with column names entity_code , parent_entity_code , entity_name and entity_role with no cycle. Having lower most child in other table named Client connected with hierarchical table's lower most child with entity_code. I have to display the data in single row hierarchy where column name will be appended by role.
Sample Example:
Tree Structure:
Hierarchical Table:
Lower Most Child's Table:
Expected Result:
Is there a way to get the expected result through oracle query? And the expected result depends on input that means it will not always start from root element, it may start from any node for example team-lead (Shail) to lowermost child.
( Note :If a upper hierachy is missing then the parent_code of the current node will be its upper hierarchy's parent_code And the missing hierarchy element will be blank in expected result.)
Thanks in Advance.
select h.Manager_entity_code
,h.Manager_entity_name
,h.Team_Lead_entity_code
,h.Team_Lead_entity_name
,h.Developer_entity_code
,h.Developer_entity_name
,c.client_name
from (select trim (both ',' from sys_connect_by_path (case when entity_role = 'Manager' then entity_code end,',')) as Manager_entity_code
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Manager' then entity_name end,',')) as Manager_entity_name
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Team-Lead' then entity_code end,',')) as Team_Lead_entity_code
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Team-Lead' then entity_name end,',')) as Team_Lead_entity_name
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Developer' then entity_code end,',')) as Developer_entity_code
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Developer' then entity_name end,',')) as Developer_entity_name
from hierarchical_table
where connect_by_isleaf = 1
connect by parent_entity_code = prior entity_code
start with entity_code = 100
) h
join client_table c
on c.entity_code =
h.Developer_entity_code
order by h.Manager_entity_code
,h.Team_Lead_entity_code
,h.Developer_entity_code
;
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
| MANAGER_ENTITY_CODE | MANAGER_ENTITY_NAME | TEAM_LEAD_ENTITY_CODE | TEAM_LEAD_ENTITY_NAME | DEVELOPER_ENTITY_CODE | DEVELOPER_ENTITY_NAME | CLIENT_NAME |
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
| 100 | Mack | 200 | Shail | 500 | Neha | Tata |
| 100 | Mack | 300 | Jack | 600 | Rocky | Rel |
| 100 | Mack | 300 | Jack | 600 | Rocky | Voda |
| 100 | Mack | 300 | Jack | 600 | Rocky | Airtel |
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
start with entity_code = 300
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
| MANAGER_ENTITY_CODE | MANAGER_ENTITY_NAME | TEAM_LEAD_ENTITY_CODE | TEAM_LEAD_ENTITY_NAME | DEVELOPER_ENTITY_CODE | DEVELOPER_ENTITY_NAME | CLIENT_NAME |
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
| (null) | (null) | 300 | Jack | 600 | Rocky | Airtel |
| (null) | (null) | 300 | Jack | 600 | Rocky | Voda |
| (null) | (null) | 300 | Jack | 600 | Rocky | Rel |
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
In your query results you expect certain roles to have a certain position in the hierarchy. Manager is first level, team leaders are second, developers are third. So you can treat your hierarchical table as if it were not. This makes the query very readable:
with manager as (select * from employee_hierarchy where entity_role = 'Manager')
, teamleader as (select * from employee_hierarchy where entity_role = 'Team-Lead')
, developer as (select * from employee_hierarchy where entity_role = 'Developer')
select
m.entity_code as manager_entity_code,
m.entity_name as manager_entity_name,
t.entity_code as team_lead_entity_code,
t.entity_name as team_lead__entity_name,
d.entity_code as developer_entity_code,
d.entity_name as developer_entity_name,
c.client_name
from manager m
join teamleader t on t.parent_entity_code = m.entity_code
join developer d on d.parent_entity_code = t.entity_code
left join client_table c on c.entity_code = d.entity_code;
And if you want to restrict the results to the department of team leader Shail, simply add the according WHERE clause:
where t.entity_name = 'Shail'
Here's one way to do this, using joins. An alternative is to combine the two tables and do a hierarchical query - but that is really the same thing (a hierarchical query is nothing but a recursive self-join).
with
hierarchical_table ( entity_code, entity_name, entity_role, parent_entity_code ) as (
select 100, 'Mack' , 'Manager', cast (null as number) from dual union all
select 200, 'Shail', 'Team-Lead', 100 from dual union all
select 300, 'Jack' , 'Team-Lead', 100 from dual union all
select 400, 'Teju' , 'Developer', 200 from dual union all
select 500, 'Neha' , 'Developer', 200 from dual union all
select 600, 'Rocky', 'Developer', 300 from dual
),
client_table ( entity_code, client_name, address ) as (
select 600, 'Voda' , 'Pune' from dual union all
select 600, 'Rel' , 'Mumbai' from dual union all
select 600, 'Airtel', 'Pune' from dual union all
select 500, 'Tata' , 'Mumbai' from dual
)
-- end of test data (not part of the solution)
-- SQL query begins BELOW THIS LINE; use your actual table names
select h1.entity_code as manager_code, h1.entity_name as manager_name,
h2.entity_code as teamlead_code, h2.entity_name as teamlead_name,
h3.entity_code as developer_code, h3.entity_name as developer_name,
c.client_name
from hierarchical_table h1 left join hierarchical_table h2
on h2.parent_entity_code = h1.entity_code
left join hierarchical_table h3
on h3.parent_entity_code = h2.entity_code
left join client_table c
on c.entity_code = h3.entity_code
where h1.parent_entity_code is null
order by manager_code, teamlead_code, developer_code, client_name
;
Output :
MANAGER_CODE MANAGER_NAME TEAMLEAD_CODE TEAMLEAD_NAME DEVELOPER_CODE DEVELOPER_NAME CLIENT
------------ ------------ ------------- ------------- -------------- -------------- ------
100 Mack 200 Shail 400 Teju
100 Mack 200 Shail 500 Neha Tata
100 Mack 300 Jack 600 Rocky Airtel
100 Mack 300 Jack 600 Rocky Rel
100 Mack 300 Jack 600 Rocky Voda
5 rows selected.
上一篇: 在分层数据中，如何选择所有节点直到根级？