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

SQL Grouping as a coma listed coloumn

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 coloumn 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