One to many relationship query result in a single row and dynamic multiple columns In a one-to-many relationship query, I would have a single row and dynamic multiple columns.
In this example the expected result is to have for each owner all the associated phone numbers in a single row, starting from a multiple rows table.
The goal is to get a json ready to be imported in a MongoDB database.
SELECT o.id, p.numbers

FROM owner o
INNER JOIN phones p ON o.id = p.owner_id

WHERE o.id=1


getting the following result
o.id     p.numbers
-----    ------ 
1        333-555-888     
1        222-777-666  
1        555-657-555
1        ....

The result should be:
o.id     p.numbers1     p.numbers2     p.numbers3     p.numbers4
-----    -----------    ----------     ----------    ----------
1        333-555-888     222-777-666   555-657-555     .......
      

This Q &amp; A MySQL single table static and dynamic pivot doesn't resolve the issue since &quot;Kode 1&quot; or &quot;Kode 2&quot; are the different phone numbers for me.
I am using MySQL 5.7.24. I want to get for each owner (t1) every phone number (t2) associated to him, in a single row with multiple columns. (Each phone number in a dedicated column) and they can be different.
I want to export to csv. There can be around 10 phone numbers per owner.
The query below returns exactly what you described in your question but I doubt this is what you really need.
SET @sql = NULL;
SELECT GROUP_CONCAT(
 CONCAT('(SELECT numbers FROM phones WHERE owner_id = ',id, ' ORDER BY numbers LIMIT 1 OFFSET ',rn - 1,' ) AS numbers', rn) 
 ) INTO @sql
FROM (SELECT o.id, p.numbers, @rn := @rn + 1 AS rn
FROM owner o
INNER JOIN phones p ON o.id = p.owner_id
CROSS JOIN (SELECT @rn := 0) r
WHERE o.id=1) phones;
SET @sql = CONCAT('SELECT id, ', @sql, ' FROM owner WHERE id = 1');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&amp;fiddle=2f7ec41adaf2acff5ca04ed93276fc3d
Query to return records for all owners:
SET @sql = NULL;
SELECT GROUP_CONCAT(
 CONCAT('(SELECT numbers FROM phones WHERE owner_id = o.id ORDER BY numbers LIMIT 1 OFFSET ',rn - 1,' ) AS numbers', rn) 
 ) INTO @sql
FROM (SELECT o1.id, p.numbers, @rn := @rn + 1 AS rn
FROM owner o1
INNER JOIN phones p ON o1.id = p.owner_id
CROSS JOIN (SELECT @rn := 0) r
WHERE o1.id=(SELECT owner_id FROM (SELECT owner_id, COUNT(1) AS cnt FROM phones GROUP BY owner_id) c ORDER BY cnt DESC LIMIT 1)) phones;
SET @sql = CONCAT('SELECT o.id, ', @sql, ' FROM owner o -- WHERE id = 2');

-- SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&amp;fiddle=0f5f425c11fa45bcc202a3ed116d5033