MySQL Pivot – One to Many Relationship Query Result in Single Row with Dynamic Columns

MySQLpivot

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.

Best 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

Related Question