[SQL] Transpose Hierarchical Table based on leaf nodes with roots as first column

This is similar to this one here. The difference is I want to display the roots in the first column (to the left)

I have a hierarchical table categories as follows:

id​

name​

parent_id​

1​

Accommodation​

null​

2​

Transport​

null​

3​

Utility​

1​

4​

Maintenance​

1​

5​

Private​

2​

6​

Public​

2​

7​

Electricity​

3​

8​

Gas​

3​

9​

Internet​

3​

10​

Garden service​

4​

11​

Repairs​

4​

12​

Car repayment​

5​

13​

Entertainment​

null​

14​

....​

..​

I want to transpose this to show one row for each leaf to be as follows (I know upfront the levels are 3 at max), with the roots appearing at the first column:

root_id​

root_name​

child_id_1​

child_name_1​

child_id_2​

child_name_2​

1​

Accommodation​

3​

Utility​

9​

internet​

1​

Accommodation​

3​

Utility​

8​

Gas​

2​

Transport​

5​

Private​

12​

Car repayment​

2​

Transport​

6​

Public​

null​

null​

12​

Entertainment​

null​

null​

null​

null​

..​

..​

..​

..​

..​

..​

..

Continue reading...