MySQL – Write a sql query to transpose fetched records?

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