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

SQL Grouping as a coma listed 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.

Facia | 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download