Question :
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 & A MySQL single table static and dynamic pivot doesn’t resolve the issue since “Kode 1” or “Kode 2” 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.
Answer :
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&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&fiddle=0f5f425c11fa45bcc202a3ed116d5033