How to make data fetched from the second table to show up as columns instead of rows?

I have two tables: table1 ( id, name, uuid) and table2 ( t_id, t_uuid, dependent_name, dependent_age).

Note: Each user can only add up to 10 dependents.

I used inner join to join these two tables which somewhat works. But, I want the records fetched from table2 to get populated as columns.

Example:

table1 record:

1, Jack, 1521

table2 records:

1, 1521, John, 12 2, 1521, Maria, 7 3, 1521, Larry, 3

The result desired is a row like:

1, Jack, John, 12, Maria, 7, Larry, 3

Here is the query that I wrote:

SELECT table1.name, table2.dependent_name, table2.dependent_age, FROM table1 INNER JOIN table2 ON table1.uuid = table2.t_uuid;

Please help, thank you.

## Answer

You could do something like below. Since it is always 10 dependents, you could code 10 aggregation for name and age. I have shown 4 below.

SELECT p.id, p.pid, p.name, MAX(CASE WHEN t.dr = 1 THEN t.name END) as name1, MAX(CASE WHEN t.dr = 1 THEN age END) as age1, MAX(CASE WHEN t.dr = 2 THEN t.name END) as name2, MAX(CASE WHEN t.dr = 2 THEN t.age END) as age2, MAX(CASE WHEN t.dr = 3 THEN t.name END) as name3, MAX(CASE WHEN t.dr = 3 THEN t.age END) as age3, MAX(CASE WHEN t.dr = 4 THEN t.name END) as name4, MAX(CASE WHEN t.dr = 4 THEN t.age END) as age4 FROM Table1 p join (select id , pid, name, age, DENSE_RANK() OVER (partition by pid order by age) as dr from Table2) t on p.pid= t.pid group by p.id, p.pid, p.name

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d324e7654218dae3d1168df2bf529099

If dense_rank() function is not working ..try this. This is the workaround for dense_Rank as it looks like mysql doesnt support dense_rank

SELECT p.id, p.pid, p.name, MAX(CASE WHEN t.dr = 1 THEN t.name END) as name1, MAX(CASE WHEN t.dr = 1 THEN age END) as age1, MAX(CASE WHEN t.dr = 2 THEN t.name END) as name2, MAX(CASE WHEN t.dr = 2 THEN t.age END) as age2, MAX(CASE WHEN t.dr = 3 THEN t.name END) as name3, MAX(CASE WHEN t.dr = 3 THEN t.age END) as age3, MAX(CASE WHEN t.dr = 4 THEN t.name END) as name4, MAX(CASE WHEN t.dr = 4 THEN t.age END) as age4 FROM Table1 p join ( SELECT pid, name, age, @dense:=IF(@prev_col1=t.pid, IF(@prev_col3=age, @dense, @dense+1), 1) AS dr, @prev_col1:=t.pid, @prev_col2:=t.name , @prev_col3:=t.age FROM (select pid, name, age from Table2 order by pid) t, (SELECT @row_num:=1, @dense:=1, @rank:=1, @prev_col1:=NULL, @prev_col2:=NULL, @prev_col3:=NULL) var ) t on p.pid= t.pid group by p.id, p.pid, p.name

http://sqlfiddle.com/#!9/6101fa/18