Karatte Karatte - 1 month ago 13
SQL Question

Oracle SQL connect_by_root and subqueries

For the past few days I'm working on a solution for my assignment and I just can't seem to find an answer.

Introduction: suppose we have a single table called People. Each person has three fields:

name
,
boss
and
position
.
name
is a primary key,
position
just a string and
boss
as a foreign key points to a different person's
name
. It creates a ordinary tree like Person1 -> Person2 -> Person3 -> Person4 , where Person4 would be a highest boss and Person1 being the root. For the simplicity let's assume that nobody has more than 3 bosses, and Person4 is the head boss (the only person with
boss
equal to
null
.)

Example paths:

Person7 -> Person4

Person6 -> Person8 -> Person4

Person2 -> Person8 -> Person4

So my assignment says: create a query which displays in hierarchical order names of each boss for every person with
position
equal to "Worker" or "Manager" using only tree operations (connect by, connect_by_root etc.) and subqueries


The output table has to consist of 5 columns:

Name | Position | Boss 1 | Boss 2 | Boss 3


If any boss column would be null, then I should insert some whitespaces.

This is my query so far:

select
case
when l = 1 then name else ' ' end as "Name",
position,
case
when l = 2 then name else ' ' end as "Boss 1",
case
when l = 3 then name else ' ' end as "Boss 2",
case
when l = 4 then name else ' ' end as "Boss 3"
from (
select
connect_by_root position as position,
level as l,
name
from
People
connect by prior
boss = name
start with
position = 'Worker'
or position = 'Manager'
);


It kinda does the trick, but each level of the tree is a new row, that's something I have to avoid. I know why this query produces result like that but I have no idea how to make it traverse the tree without creating new row on each step.

My result:

Name |Position|Boss 1|Boss 2|Boss 3

JOHN WORKER
WORKER HENRY
WORKER PETER
TERRY WORKER
WORKER PETER
ALICE WORKER
WORKER PETER
BILL MANAGER
MANAGER JAMES
MANAGER PETER


This is the result I want to achieve:

Name |Position|Boss 1|Boss 2|Boss 3

JOHN WORKER HENRY PETER

TERRY WORKER PETER

ALICE WORKER PETER

BILL MANAGER JAMES PETER


Is there any solution without using sophisticated functions like Pivot to make it work?

Answer

It should work like this (I have no source data to check):

select  
  root_name as "Name",
  max(position),
  max(case when l = 2 then name else null end) as "Boss 1",
  max(case when l = 3 then name else null end) as "Boss 2",
  max(case when l = 4 then name else null end) as "Boss 3"    
from (
  select
    connect_by_root position as position, 
    connect_by_root name as root_name, 
    level as l,
    name 
  from 
    People
  connect by prior
    boss = name
  start with 
    position = 'Worker'
    or position = 'Manager'
  )
group by root_name;