CODEWITHSUNDEEP
mysqlsqlparent-childcommon-table-expressionhierarchy
Stalin Thomas
Stalin Thomas

Reputation: 409

View Hierarchical data as columns in sql

I have a self referencing table to store a hierarchy.

vehicle_id vehicle_name parent_vehicle_id
1 social_media NULL
2 facebook 1
3 instagram 1
4 stories 2
5 reel 3
6 igtv 3

using a cte, I am able to get the vehicle levels like so

with vehicle_tree (vehicle_id, vehicle_name, parent_vehicle_id, veh_level)
as
(
select v.vehicle_id, v.vehicle_name, v.parent_vehicle_id, 1 as veh_level from vehicle as v where v.parent_vehicle_id is null
union all
select sub_veh.vehicle_id, sub_veh.vehicle_name, sub_veh.parent_vehicle_id, vt.veh_level + 1 from vehicle as sub_veh 
inner join vehicle_tree as vt
on vt.vehicle_id = sub_veh.parent_vehicle_id
where sub_veh.parent_vehicle_id is not null
)
select * from vehicle_tree

with the output

vehicle_id vehicle_name parent_vehicle_id veh_level
1 social_media NULL 1
2 facebook 1 2
3 instagram 1 2
5 reel 3 3
6 igtv 3 3
4 stories 2 3

What I would like to have, is to view the hierarchies as columns rather than rows something like this

1_id 1 2_id 2 3_id 3
1 social_media 2 facebook 4 stories
1 social_media 3 instagram 5 reel
1 social_media 3 instagram 6 igtv

I have gotten as far as this

with vehicle_tree (vehicle_id, vehicle_name, parent_vehicle_id, veh_level)
as
(
select v.vehicle_id, v.vehicle_name, v.parent_vehicle_id, 1 as veh_level from vehicle as v where v.parent_vehicle_id is null
union all
select sub_veh.vehicle_id, sub_veh.vehicle_name, sub_veh.parent_vehicle_id, vt.veh_level + 1 from vehicle as sub_veh 
inner join vehicle_tree as vt
on vt.vehicle_id = sub_veh.parent_vehicle_id
where sub_veh.parent_vehicle_id is not null
)
select * from vehicle_tree l1
left outer join vehicle_tree l2 on l2.veh_level = 2 and l1.vehicle_id = l2.parent_vehicle_id
left outer join vehicle_tree l3 on l3.veh_level = 3 and l2.vehicle_id = l3.parent_vehicle_id
where l1.veh_level = 1

which gives the output that I expect but I would like to have some form of a recursive solution on the left outer joins instead

vehicle_id vehicle_name parent_vehicle_id veh_level vehicle_id vehicle_name parent_vehicle_id veh_level vehicle_id vehicle_name parent_vehicle_id veh_level
1 social_media NULL 1 2 facebook 1 2 4 stories 2 3
1 social_media NULL 1 3 instagram 1 2 5 reel 3 3
1 social_media NULL 1 3 instagram 1 2 6 igtv 3 3

Kindly advise how I can achieve the same output via a recursive function. I would prefer a solution that could be used on any engine but MS SQL will suffice for now

Upvotes: 0

Views: 868

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

Because a SQL query returns a fixed set of columns, you can just use explicit JOINs to get the names in different columns:

select vt.*, vt2.*, vt3.*
from vehicle_tree vt left join
     vehicle_tree vt2
     on vt2.parent_vehicle_id = vt.vehicle_id left join
     vehicle_tree vt3
     on vt3.parent_vehicle_id = vt2.vehicle_id left join
where vt.parent_vehicle_id is null

Upvotes: 1

Related Questions