report_generator Posted August 20, 2019 Share Posted August 20, 2019 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? Link to comment Share on other sites More sharing options...
DerekGannaway Posted August 20, 2019 Share Posted August 20, 2019 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: StateCityNew YorkNew York CityNew YorkSyracuseCaliforniaLos AngelesCaliforniaSan FranciscoTexasAustin With the below querySELECT state, GROUP_CONCAT(city SEPARATOR ", ") AS cities FROM table GROUP BY state[/code]The data will now be displayed asstatecitiesNew YorkNew York City, SyracuseCaliforniaLos Angeles, San FranciscoTexasAustin 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now