Only keep first value of first column and sort the two other columns according to hierarchy

Question

So five people work at the Laugh factory. Marc is up first, then Judy, then Lorie, then Agnes and lastly, Isaiah.

I want to go from this:

+------------------------------+
| Laugh Factory |Marc | Judy   |
| Laugh Factory |Judy | Lorie  |
| Laugh Factory |Lorie| Agnes  |
| Laugh Factory |Agnes| Isaiah |
+------------------------------+

To this:

 Laugh Factory | Marc | Judy | Lorie | Agnes | Isaiah

How do I achieve that?

Answer 1

You need a recursive query for that.

Something along the lines (untested):

with recursive tree as (
  select company, comedian, preceding_comedian, 1 as level
  from the_table
  where company = 'Laugh Factory'
    and preceding_comedian is null
  union all
  select ch.company, ch.comedian, ch.preceding_comedian, p.level + 1
  from the_table ch
    join tree p on ch.preceding_comedian = p.comedian
)
select company, string_agg(comedian, ' > ' order by level) as comedians
from tree 
group by company;