Trying to obtain the accurate information (CTE - recursive)

Question

I have different tables and the goal is to obtain the approval workflow for every customer, displaying that information in this way:

> CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4

First of all, i have a table called entities

(12, 'Math Andrew', 308, 'CHAIN1-MathAndrew')
(13, 'John Connor', 308, 'CHAIN2-JohnConnor')
(18, 'ZATCH', 309, null),
(19, 'MAX', 309, null),
(20, 'Ger',310, null),
(21, 'Mar',310, null),
(22, 'Maxwell',311, null),
(23, 'Ryan',312, null),
(24, 'Juy',313, null),
(25, 'Angel',314, null),
(26, 'John',315, null);

DO NOTE:

12 was assigned to Math Andrew... 308 is the number that says that Matt Andrew is a CLIENT

13 was assigned to John Connor... 308 is the number that says that John Connor is a CLIENT

Because Math Andrew and John Connor are CLIENTS (also known as CUSTOMERS) they must be linked to one or more APPROVERS

A client could have 1 APPROVER, OR 2 APPROVERS OR 3 APPROVERS OR 4 APPROVERS, there exist different approvers inside entities table.

When i say that a client "could have" 1 or more APPROVERS i mean this

CLIENT - APPROVER4 (this is a 1-1 relationship) PS: A CLIENT WILL ALWAYS BE RELATED TO the APPROVER4 IN SOME WAY OR ANOTHER

CLIENT - APPROVER1 - APPROVER4 (in this case there Will be 2 relations.. ONE: CLIENT-APPROVER1 and another APPROVER1-APPROVER4)

CLIENT - APPROVER1 - APPROVER2 - APPROVER4 (in this case there Will be 3 relations.. ONE: CLIENT-APPROVER1, APPROVER1- APPROVER2 AND APPROVER2 - APPROVER4)

AND SO ON... (hopefully you get the idea)

table type_entities

(308,'CLIENT'),
(309,'APPROVER1'),
(310,'APPROVER2'),
(311,'APPROVER3'),
(312,'J3 APPROVER4'),
(313,'J4 APPROVER4'),
(314,'J5 APPROVER4'),
(315, 'J6 APPROVER4'),
(316,'J7 APPROVER4');

table type_relation

(444,'J6 CLIENT-APPROVER4'),
(445,'J3 CLIENT-APPROVER4'),
(446,'J4 CLIENT-APPROVER4'),
(447,'J10 CLIENT-APPROVER4'),
(449,'J5 CLIENT-APPROVER4'),
(453,'J5 CLIENT-APPROVER4'),
(456,'J7 CLIENT-APPROVER4'),
(457,'J8 CLIENT-APPROVER4'),
(458,'CLIENT-APPROVER3'),
(459,'CLIENT-APPROVER1'),
(460,'APPROVER1-APPROVER2'),
(461,'APPROVER1-APPROVER3'),
(462,'J3 APPROVER1-APPROVER4'),
(463,'APPROVER2-APPROVER3'),
(464,'J3 APPROVER3-APPROVER4'),
(465,'J4 APPROVER3-APPROVER4'),
(466,'J5 APPROVER3-APPROVER4'),
(467,'J6 APPROVER3-APPROVER4'),
(468,'J7 APPROVER3-APPROVER4'),
(469,'J8 APPROVER3-APPROVER4'),
(470,'J10 APPROVER3-APPROVER4'),
(471,'CLIENT-APPROVER2');

---

Types of relations:

CLIENT - APPROVER1 : (459,'CLIENT-APPROVER1')

CLIENT - APPROVER2 : (471,'CLIENT-APPROVER2')

CLIENT - APPROVER3 : (461,'APPROVER1-APPROVER3')

CLIENT - APPROVER4:

(445,'J3 CLIENT-APPROVER4')

(446,'J4 CLIENT-APPROVER4')

(449,'J5 CLIENT-APPROVER4')

(444,'J6 CLIENT-APPROVER4')

(456,'J7 CLIENT-APPROVER4')

(457,'J8 CLIENT-APPROVER4')

(447,'J10 CLIENT-APPROVER4')

APPROVER 1 -APPROVER 2:

(460,'APPROVER1-APPROVER2')

APPROVER 2 - APPROVER 3:

(463,'APPROVER2-APPROVER3')

APPROVER 3 - APPROVER 4:

(464,'J3 APPROVER3-APPROVER4')

(465,'J4 APPROVER3-APPROVER4')

(466,'J5 APPROVER3-APPROVER4')

(467,'J6 APPROVER3-APPROVER4')

(468,'J7 APPROVER3-APPROVER4')

(469,'J8 APPROVER3-APPROVER4')

(470,'J10 APPROVER3-APPROVER4')

---

THIS IS IMPORTANT: when a client is linked to one approver, a NEW RELATION is created inside relationships table.

Table relationships:

(787,459,12,18)
(788,460,18,20)
(789,463,20,21)
(790,467,21,26)

787 IS THE NUMBER THAT WAS ASSIGNED WHEN THAT ROW WAS CREATED
459 REPRESENTS THE RELATION: CLIENT - APPROVER
CHAIN1-MathAndre is theclient
18 is the approver

Following the idea:

APPROVER1 was linked to APPROVER2

(788,460,18,20)

APPROVER2 was linked to APPROVER3

(789,463,20,21)

APPROVER3 was linked to APPROVER4

(790,467,21,26) ​

So, i would like to display this in screen:

|CLIENT               | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4|
|CHAIN1-MathAndrew    |   ZATCH   |   Ger     |    Mar    |    John  |
|CHAIN2-JohnConnor    |    MAX    |           |    Mario  |    Steven|
|CHAIN3-MarioShapiro  |    IVAN   |           |           |    John  |

The last 2 rows are just an example

---

This is what i have so far (it IS working):

LINK_sample_SQL

but it is displaying the information without showing columns name (CLIENT, APPROVER1, APPROVER2, APPROVER3, APPROVER4).. This is displaying this:

CHAIN1-MathAndrew-ZATCH-Ger-Mar-John

I want to display the data in this way:

|CLIENT               | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4|
|CHAIN1-MathAndrew    |   ZATCH   |   Ger     |    Mar    |    John  |
|CHAIN2-JohnConnor    |    MAX    |           |    Mario  |    Steven|
|CHAIN3-MarioShapiro  |    IVAN   |           |           |    John  |

I am pretty lost, could you please help me?

EDIT:

The maximum amount of approvers is: 4

Answer 1

You should use conditional aggregation to format your data as you want. Try the following solution, where I assume that you have MySQL ver.8 and the window functions are available:

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

DB-FIDDLE DEMO

The solution uses your SQL query and adds the necessary information for table formating: (1) row_id, and (2) col_id. These values are then used in conditional aggeration to create the table.