The Old County The Old County - 2 months ago 11
MySQL Question

SQL Grouping as a coma listed column

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

SELECT
`sample`.Fascia AS 'Fascia',
`sample`.Category AS 'Category'
FROM
`sample`
WHERE
`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

use Group_concat:

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