CODEWITHSUNDEEP
sql
user3366515
user3366515

Reputation: 31

SQL View to flatten out a hierarchy

I have a table with some parent child relationships. I want to create a view that has all possible ids for a location id.

I need the sql to do this

Table:

ID  PARENT_ID   LOCATION_ID
1   NULL        ABC
2   1           XYZ
3   NULL        EFG

view results:

LOCATION_ID     ID
XYZ             1
XYZ             2
ABC             1
ABC             2
EFG             3

Upvotes: 1

Views: 102

Answers (1)

The Impaler
The Impaler

Reputation: 48865

You don't mention the database you are using, so I'll assume PostgreSQL. You can adjust the answer to your specific engine:

with recursive
n as (
  select id, id as grp, location_id from t where parent_id is null
  union all
  select t.id, n.grp, t.location_id
  from n
  join t on t.parent_id = n.id
)
select b.id, a.location_id
from n a
join n b on a.grp = b.grp

Result:

id location_id
-- -----------
1  ABC
2  ABC
1  XYZ
2  XYZ
3  EFG

For the record, the data script I used is:

create table t (
  id int,
  parent_id int,
  location_id varchar(10)
);

insert into t (id, parent_id, location_id) values 
  (1, null, 'ABC'),
  (2, 1,    'XYZ'),
  (3, null, 'EFG');

Upvotes: 1

Related Questions