The Old County The Old County - 1 year ago 57
MySQL Question

SQL Grouping as a comma-separated list of values in one column

I am working on an API to return a list of venues.

`sample`.Fascia AS 'Fascia',
`sample`.Category AS 'Category'
`sample`.`PostCode` LIKE '%SW1%'

and this returns a list of venues.

Fascia | Category
Sainsbury's | Supermarkets
Waitrose | Supermarkets
99p Store | Hardware
T K Max | Clothing

How would I modify the query to group the categories together and create an abstract column that lists these venues as a comma list. So something like this

Category | Grouped Venues
Supermarkets | Sainsbury's, Waitrose
Hardware | 99p Store
Clothing | T K Max

Answer Source

use Group_concat:

        Group_concat(`sample`.Fascia) AS 'Grouped Venues',
        `sample`.Category AS 'Category'
        WHERE `sample`.`PostCode` LIKE '%SW1%'
        Group by `sample`.Category