MySQL iterate information from one table in to 2 columns in query

Question

I have a database which holds maintenance reports for unique units that I need to pull information from in a single query. My issue is the convoluted way the information is stored.

SELECT date(reports.created_timestamp) as Date,
        locations.name as Location,
        location_groups.name as L_group,
        units.name as Unit_name,
        units.id as Unit_ID,
        concat(users_2.first_name, "           
                    ",users_2.last_name) as Engineer,
        reports.id as Report_ID 
FROM reports
LEFT JOIN units
    ON reports.unit_id = units.id
LEFT JOIN users_2
    ON reports.user_id = users_2.id
LEFT JOIN locations
    ON units.location_id = locations.id
LEFT JOIN location_groups
    ON locations.locations_group_id = location_groups.id
where reports.created_timestamp >= '2022-01-01 00:00:00'
and locations.name NOT LIKE 'Company Vehicles'
and location_groups.name = 'Airports';

This returns:

Date	Location	L_group	Unit_name	Unit_ID	Engineer	Report_ID
2022-10-18	Airport 1	Airports	AIR-AAA1-N127	1	Engineer 1	19471
2022-10-19	Airport 2	Airports	AIR-BBB2-D03	4	Engineer 2	19486
2022-10-19	Airport 2	Airports	AIR-BBB2-D14	13	Engineer 2	19495
2022-10-25	Airport 2	Airports	AIR-BBB2-D13	14	Engineer 2	19518

However, when the engineers are completing maintenance they have to do several tasks depending on the "unit" type. This is held in the units table as unit_type_id variable. This then references display_type_report_tasks to determine what tasks should be completed based on the unit_type_id, e.g:

id	unit_type_id	report_task_id
1	3	1
2	13	1
9	13	2
3	14	1
4	15	1

The report_task_id's then relate to table report_tasks which holds the following as an example:

id	name	text	description
1	Unit Cleaned-Internal	Unit Cleaned-Internal	Unit Cleaned-Internal
2	Unit Cleaned-External	Unit Cleaned-External	Unit Cleaned-External
3	Bumper Bar Fixed	Bumper Bar Fixed	Bumper Bar Fixed
4	Tile Replaced	Tile Replaced	Tile Replaced
5	Hardware Replaced	Hardware Replaced	Hardware Replaced

Picking unit type 13 as an example, when an engineer completes a report, the task options are stored in reports_tasks_selected_options such as the following:

id	report_tasks_option_id	example_report_id
30452	1	19558
30453	9	19558

The report_tasks_option_id then relates to report_tasks_options:

id	value	description	report_task_id
1	1	Not cleaned	1
2	2	Internal Clean	1
3	3	Deep Clean	1
7	1	Not cleaned	2
8	2	External Clean	2
9	3	Deep Clean	2

I want to query the unit_type_id and dynamically assign the column headers using report_tasks.name depending on what unit types are returned. (If a unit is returned that doesn't utilise all columns then NULL is used where no result is returned.)

I have to build reports per unit_type and statically assign the columns, BUT I cannot figure out how to then iterate through reports_tasks_selected_options to get the report_tasks_options.description for the results. As an example (using report_id = 19558) my current query output would be:

Date	Location	L_group	Unit_name	Unit_ID	Engineer
2022-11-01	Airport 3	Airports	AIR-CCC1-D09	1907	Engineer 3

But because I know this is unit_type=13 I know that the report_tasks related to it are Unit Cleaned-Internal and Unit Cleaned-External. So I want to iterate through the reports_tasks_selected_options table for that report number (As I know that the lower ID is always Unit Cleaned-Internal because it has the lower ID in report_tasks, and so the higher ID from reports_tasks_selected_options is always going to be Unit Cleaned-External.)

Desired output:

Date	Location	L_group	Unit_name	Unit_ID	Engineer	Report_ID	Clean-Int	Clean-Ext
2022-11-01	Airport 3	Airports	AIR-CCC1-D09	1907	Engineer 3	19558	Not cleaned (1)	Deep Clean (9)

I put the option numbers in brackets for clarity but do not require them in my output.