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

Question

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

Jens · Accepted Answer · 2016-08-17 10:47:13Z

1

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

answered Aug 17, 2016 at 10:47

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related