SQL: Reverse tree traversal on single result

user_143960 0
Viewed 266

My table looks like this:

id | name | type_id | desc | parent_id
1  | Foo  |    1    | Foo  | NULL
2  | Bar  |    2    | Bar  | 1
3  | FB   |    2    | FB   | 1
4  | Foo1 |    1    | Foo1 | NULL
5  | Bar1 |    2    | Bar1 | 4
6  | FB1  |    2    | FB1  | 4

And I want to provide an ID of the lowest node, returning everything up to the highest node in a single row (There is other data that I'm returning along with this). For example, I want to provide ID 3, and the results to look like so:

xxxxx (other data) | id  | name  | type_id | desc | parent_id | id | name | type_id | desc | parent_id
xxxxxxx            | 3   | FB    |   2     | FB   |    1      | 1  | Foo  |    1    | Foo  | NULL

Unfortunately, I haven't found anything that can work for me. I have a CTE but it goes top down and each node is its own row:

WITH RECURSIVE cte AS (
  select T.*
  from table as T
  where T.id = 3
  union all
  select T.*
  from table as T
    inner join cte as C
      on T.parent_id = C.id
)
SELECT * FROM cte

When I do this, I only get one result:

id | name | type_id | desc | parent_id
3  | FB   |    2    | FB   | 1

Any help would be appreciated, thanks!

recursive-query hierarchical-data postgresql sql
2 Answers

The logic of the common-table expression looks good; it generates one row for the original id, and then one row per parent. To pivot the resulting rows to columns, you can then use conditional aggregation - this requires that you decide in advance the maximum number of levels. For two levels, this would be:

with recursive cte as (
    select t.*, 1 lvl
    from table as t
    where t.id = 3
    union all
    select t.*, c.lvl + 1
    from table as t
    inner join cte as c on t.parent_id = c.id
)
select 
    max(id)        filter(where lvl = 1) id,
    max(name)      filter(where lvl = 1) name,
    max(type_id)   filter(where lvl = 1) type_id,
    max(descr)     filter(where lvl = 1) descr,
    max(parent_id) filter(where lvl = 1) parent_id,
    max(id)        filter(where lvl = 2) id2,
    max(name)      filter(where lvl = 2) name2,
    max(type_id)   filter(where lvl = 2) type_id2,
    max(descr)     filter(where lvl = 2) descr2,
    max(parent_id) filter(where lvl = 2) parent_id2,
from cte

You might also want to consider accumating the rows as an array of json objects:

with recursive cte as (
    select t.*, 1 lvl
    from table as t
    where t.id = 3
    union all
    select t.*, c.lvl + 1
    from table as t
    inner join cte as c on t.parent_id = c.id
)
select jsonb_agg(to_jsonb(c) order by lvl) res
from cte c
user_106767160

I have used Oracle 11g using Pivot, Row_number and hierarchical queries to solve this.

Demo

WITH CTE1 AS (SELECT A.*, LEVEL AS LVL FROM TABLE1 A
START WITH ID IN (2,3)
CONNECT BY PRIOR PARENT_ID = ID)
select * from (
select x.*, row_number() over (order by id desc) rn from (
SELECT DISTINCT ID, NAME, TYPE_ID, DESCRIPTION, PARENT_ID FROM CTE1 ORDER BY ID DESC) x) y
pivot
( min(id) ID, min(name) name, min(type_id) type_id, 
 min(description) description, min(parent_id) for rn in (1, 2, 3)
  );
user_109082740
Related