CODEWITHSUNDEEP
mysql
The Old County
The Old County

Reputation: 89

SQL Grouping as a comma-separated list of values in one 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.

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

Upvotes: 0

Views: 38

Answers (1)

Jens
Jens

Reputation: 69440

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

Upvotes: 1

Related Questions