MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table ticket_categories  (cat_id int, cat_parent_id int, cat_level int, cat_name varchar(200));
insert into ticket_categories  values
(1, 0, 0, 'People and Culture'),
(2, 0, 0, 'Information Technology'),
(3, 0, 0, 'Admin and Facilities'),
(4, 3, 1, 'Safety and Security Related Concerns'),
(5, 2, 1, 'User Account'),
(6, 1, 1, 'Employee Benefit Requests'),
(7, 6, 2, 'SSS Loan Inquirey'),
(8, 5, 2, 'Enable / Disable Access'),
(9, 4, 2, 'CCTV Footage Request'),
(10, 1, 1, 'Certification'),
(11, 10, 2, 'Certificate of Employment'),
(12, 10, 2, 'SSS Certificate of Contributions');
select a.cat_id, a.cat_level, a.cat_name, 
    concat(
        ifnull(concat(c.cat_name, '  '), ''),
        ifnull(concat(b.cat_name, '  '), ''),
        a.cat_name) as cat_order
from ticket_categories a
left join ticket_categories b on b.cat_id = a.cat_parent_id 
left join ticket_categories c on c.cat_id = b.cat_parent_id 
order by cat_order;


cat_id
cat_level
cat_name
cat_order


3
0
Admin and Facilities
Admin and Facilities

4
1
Safety and Security Related Concerns
Admin and Facilities  Safety and Security Related Concerns

9
2
CCTV Footage Request
Admin and Facilities  Safety and Security Related Concerns  CCTV Footage Request

2
0
Information Technology
Information Technology

5
1
User Account
Information Technology  User Account

8
2
Enable / Disable Access
Information Technology  User Account  Enable / Disable Access

1
0
People and Culture
People and Culture

10
1
Certification
People and Culture  Certification

11
2
Certificate of Employment
People and Culture  Certification  Certificate of Employment

12
2
SSS Certificate of Contributions
People and Culture  Certification  SSS Certificate of Contributions

6
1
Employee Benefit Requests
People and Culture  Employee Benefit Requests

7
2
SSS Loan Inquirey
People and Culture  Employee Benefit Requests  SSS Loan Inquirey


with recursive cte(cat_id, cat_level, cat_name, cat_order) as (
  select cat_id, cat_level, cat_name, cat_name
  from ticket_categories
  where cat_parent_id = 0
  union
  select t.cat_id, t.cat_level, t.cat_name, concat(cte.cat_order, '  ', t.cat_name)  
  from cte
  inner join ticket_categories t on t.cat_parent_id = cte.cat_id
)
select * from cte
order by cat_order;


cat_id
cat_level
cat_name
cat_order


3
0
Admin and Facilities
Admin and Facilities

4
1
Safety and Security Related Concerns
Admin and Facilities  Safety and Security Related Concerns

9
2
CCTV Footage Request
Admin and Facilities  Safety and Security Related Concerns  CCTV Footage Request

2
0
Information Technology
Information Technology

5
1
User Account
Information Technology  User Account

8
2
Enable / Disable Access
Information Technology  User Account  Enable / Disable Access

1
0
People and Culture
People and Culture

10
1
Certification
People and Culture  Certification

11
2
Certificate of Employment
People and Culture  Certification  Certificate of Employment

12
2
SSS Certificate of Contributions
People and Culture  Certification  SSS Certificate of Contributions

6
1
Employee Benefit Requests
People and Culture  Employee Benefit Requests

7
2
SSS Loan Inquirey
People and Culture  Employee Benefit Requests  SSS Loan Inquirey

cat_id	cat_level	cat_name	cat_order
3	0	Admin and Facilities	Admin and Facilities
4	1	Safety and Security Related Concerns	Admin and Facilities Safety and Security Related Concerns
9	2	CCTV Footage Request	Admin and Facilities Safety and Security Related Concerns CCTV Footage Request
2	0	Information Technology	Information Technology
5	1	User Account	Information Technology User Account
8	2	Enable / Disable Access	Information Technology User Account Enable / Disable Access
1	0	People and Culture	People and Culture
10	1	Certification	People and Culture Certification
11	2	Certificate of Employment	People and Culture Certification Certificate of Employment
12	2	SSS Certificate of Contributions	People and Culture Certification SSS Certificate of Contributions
6	1	Employee Benefit Requests	People and Culture Employee Benefit Requests
7	2	SSS Loan Inquirey	People and Culture Employee Benefit Requests SSS Loan Inquirey