您应该使用条件聚合来格式化所需的数据。尝试以下解决方案,假设您拥有MysqL ver.8,并且窗口功能可用:
WITH recursive relationships_CTE as (
select e.id, e.description AS name, 1 col_id,
row_number() over (order by e.id) row_id
from entities e
where e.description like 'CHAIN%'
UNION ALL
select r.description_entitiy_2, e.name, col_id+ 1, row_id
from relationships_CTE cte
left join relationships r
on r.description_entitiy_1 = cte.id
join entities e
on r.description_entitiy_2 = e.id
)
select
max(case when col_id = 1 then name end) client,
max(case when col_id = 2 then name end) approver1,
max(case when col_id = 3 then name end) approver2,
max(case when col_id = 4 then name end) approver3,
max(case when col_id = 5 then name end) approver4
from relationships_CTE
group by row_id
该解决方案使用您的SQL查询并添加必要的信息以进行表格式化:(1)row_id和(2)col_id。然后将这些值用于条件放大以创建表。