How to convert values in certain colum to comma separated list?

0

I have taken a  table in which rows are being repeated because of multiple values in a certain column. Is there any way that i can convert the column which is causing repetition to comma separated list so that my other column values donot repeat unnecessarily?

report_generator's picture
Joined: Aug 19 2019 - 11:40pm
Last seen: 1 month 13 hours ago

1 Answer:

0

In your query you can use the GROUP_CONCAT function and GROUP BY the field you don't want duplicated to achieve that result. Here's an example table with duplicated data:

 

State City
New York New York City
New York Syracuse
California Los Angeles
California San Francisco
Texas Austin

 

With the below query

SELECT state, GROUP_CONCAT(city SEPARATOR ", ") AS cities FROM TABLE GROUP BY state

The data will now be displayed as

state cities
New York New York City, Syracuse
California Los Angeles, San Francisco
Texas Austin

You can also sort the cities or remove duplicates from that field as well with additional functions inside the GROUP_CONCAT. More information about GROUP_CONCAT can be found at https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat

DerekGannaway's picture
Joined: Mar 17 2017 - 9:58am
Last seen: 2 weeks 32 min ago
Feedback
randomness