Oracle SQL connect_by_root and subqueries
<p>For the past few days I'm working on a solution for my assignment and I just can't seem to find an answer.</p>

<p>Introduction: suppose we have a single table called <strong>People</strong>. Each person has three fields: <code>name</code> , <code>boss</code> and <code>position</code>. <code>name</code> is a primary key, <code>position</code> just a string and <code>boss</code> as a foreign key points to a different person's <code>name</code>. 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 <code>boss</code> equal to <code>null</code>.)</p>

<p>Example paths: </p>

<p>Person7 -> Person4</p>

<p>Person6 -> Person8 -> Person4</p>

<p>Person2 -> Person8 -> Person4</p>

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

<p>The output table has to consist of 5 columns:</p>

<pre><code>Name | Position | Boss 1 | Boss 2 | Boss 3
</code></pre>

<p>If any boss column would be null, then I should insert some whitespaces.</p>

<p>This is my query so far:</p>

<pre><code>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'
  );
</code></pre>

<p>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.</p>

<p>My result:</p>

<pre><code>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     
</code></pre>

<p>This is the result I want to achieve:</p>

<pre><code>Name |Position|Boss 1|Boss 2|Boss 3

JOHN    WORKER  HENRY  PETER        

TERRY   WORKER  PETER                   

ALICE   WORKER  PETER                   

BILL    MANAGER JAMES  PETER                
</code></pre>

<p>Is there any solution <strong>without</strong> using sophisticated functions like Pivot to make it work?</p>