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.
GROUP_CONCAT(...)
be good enough for your purposes? It won't give you separate columns, but you can useDISTINCT
to get rid of dups andSEPARATOR
to keep the numbers apart. What will be done with the CSV? That is, why are separate columns any better than some other syntax?