MYSQL combine duplicate rows in left join into one row with all data

Question

I am attempting to format an SQL query using multiple left joins to a readable CSV file. When I do my left joins, it returns multiple rows for the same ID (as it should, because there are multiple non-unique rows being joined with it), however this is not acceptable for a readable CSV file.

What I want is for each duplicate row to be combined into one row with multiple columns for the duplicate data.

Current join results example:

SELECT *
FROM people
LEFT JOIN attributes on ID

ID | Name | Attributes
1  | Ken  | Tall
1  | Ken  | Slender
1  | Ken  | Blonde
2  | John | Short

Desired results (exported to CSV):

ID | Name | Attribute 1 | Attribute 2 | Attribute 3
1    Ken    Tall          Slender       Blonde
2    John   Short

I also attempted group by ID, but when I do that it just returns one of the attributes for each ID, which is also not acceptable.

Maybe I'm not looking in the right places but I cannot seem to find any functions to help me accomplish this.

Thanks in advance!!!

Answer 1

You can also use GROUP_CONCAT, no need to know the maximum number of attributes, and ok for csv import.

SELECT id, 
       name,
       GROUP_CONCAT(attributes SEPARATOR ';') AS attributes 
FROM people 
GROUP BY id,
         name;