2

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.

3
  • Rather than a full "pivot", would GROUP_CONCAT(...) be good enough for your purposes? It won't give you separate columns, but you can use DISTINCT to get rid of dups and SEPARATOR to keep the numbers apart. What will be done with the CSV? That is, why are separate columns any better than some other syntax?
    – Rick James
    Jul 5, 2020 at 16:25
  • you have a mistake in description, you have conditions t1.id = t2.id and returned rows like t1.id = 1 where t2.id = 35. Please add correct table definitions with data sample and desired result. Probably you still may use group_concat. Otherwise you will need to use a lot of subqueries and may be even dynamic SQL if you don't know exact number of columns. Jul 5, 2020 at 16:29
  • @Nikita. yes, you're are right. In true, in t1 is joined to the t2.t1foreign key.
    – Alex
    Jul 5, 2020 at 16:33

1 Answer 1

1

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

2
  • It works perfectly. Thanks for the explanation in the link. Very helpful to understand the query. How it should be changed to list all the owners in the same way? I guess changing the WHERE conditions.
    – Alex
    Jul 7, 2020 at 22:10
  • @Alex, it's a bit more tricky because owners may have different amount of phones. I modified query (and dbfiddle) to create a select statement which would return maximum number of phone numbers, now you can remove WHERE clause to return rows for all owners. Jul 7, 2020 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.