Displaying Oracle Tree Tree Hierarchy Data in a Single Column

Question

I have a hierarchical table named Employee_Hierarchy in oracle with column names entity_code , parent_entity_code , entity and entity without loop. If the lower part of most children in another table named Client is associated with a hierarchical table, then the lower part of the child itself with entity_code. I have to display the data in a single row hierarchy where the column name will be added by the role.

Example example:

Tree structure:

Hierarchical table:

Bottom of the table for children:

Expected Result:

Is there a way to get the expected result through an oracle request? And the expected result depends on the input, which means that it will not always start from the root element, it can start from any node, for example, from the command line (Shail) to the very bottom.

( Note : If there is no top hierarchy, then the parent_code of the current node will be its top parent_code hierarchy. And the missing hierarchy element will be empty in the expected result.)

Thanks at Advance.

Answer 1

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 | +---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+ 

For 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 | +---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+ 

Answer 2

In the query results, you expect certain roles to have a certain position in the hierarchy. The manager is the first level, the team leaders are the second, the developers are the third. This way you can process your hierarchical table as if it weren’t. This makes the request 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 limit the results in the Shail Team Leader department, just add the appropriate WHERE :

 where t.entity_name = 'Shail' 

Answer 3

Here is one way to do this using joins. An alternative is joining two tables and executing a hierarchical query - but it's really the same thing (a hierarchical query is nothing more than 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 ; 

Exit

 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. 

Answer 4

 select regexp_substr (h.entity,'Manager~([^~]*)~' ,1,1,'',1) as Manager_entity_code ,regexp_substr (h.entity,'Manager~([^~]*)~([^,]*)' ,1,1,'',2) as Manager_entity_name ,regexp_substr (h.entity,'Team-Lead~([^~]*)~' ,1,1,'',1) as Team_Lead_entity_code ,regexp_substr (h.entity,'Team-Lead~([^~]*)~([^,]*)',1,1,'',2) as Team_Lead_entity_name ,regexp_substr (h.entity,'Developer~([^~]*)~' ,1,1,'',1) as Developer_entity_code ,regexp_substr (h.entity,'Developer~([^~]*)~([^,]*)',1,1,'',2) as Developer_entity_name ,c.client_name from (select sys_connect_by_path (entity_role || '~' || entity_code || '~' || entity_name,',') as entity ,entity_code from hierarchical_table where connect_by_isleaf = 1 connect by parent_entity_code = prior entity_code start with entity_code = 200 ) h join client_table c on c.entity_code = h.entity_code order by Manager_entity_code ,Team_Lead_entity_code ,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 | +---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+ 

For start with entity_code = 600

 +---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+ | MANAGER_ENTITY_CODE | MANAGER_ENTITY_NAME | TEAM_LEAD_ENTITY_CODE | TEAM_LEAD_ENTITY_NAME | DEVELOPER_ENTITY_CODE | DEVELOPER_ENTITY_NAME | CLIENT_NAME | +---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+ | (null) | (null) | (null) | (null) | 600 | Rocky | Airtel | | (null) | (null) | (null) | (null) | 600 | Rocky | Voda | | (null) | (null) | (null) | (null) | 600 | Rocky | Rel | +---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+