SQL Group search results from query-CodePudding

I have the results from a select statement as seen below:

 ----------- ------------------------- 
| city      | category                |
 ----------- ------------------------- 
| Aurora    | Indian                  |
| Aurora    | Pakistani               |
| Aurora    | Restaurants             |
| Avondale  | Health & Medical        |
| Avondale  | Optometrists            |
| Brampton  | Beauty & Spas           |
| Brampton  | Hair Salons             |
| Brampton  | Indian                  |
| Brampton  | Nail Salons             |
 ----------- -------------------------

I want to try group the results to something like this:

 ----------- ------------------------------------------------- 
| city      | category                                        |
 ----------- ------------------------------------------------- 
| Aurora    | Indian, Pakistani, Restaurants                  |
| Avondale  | Health & Medical, Optometrists                  |
| Brampton  | Beauty & Spas, Hair Salons, Indian, Nail Salons |
 ----------- -------------------------------------------------

The code I used to extract the initial columns results was this SQL query:

SELECT B.City, C.Category
FROM Business B
INNER JOIN Category C
ON B.id = C.business_id

Any help would be appreciated, thank you

CodePudding user response：

You can do it by group by as:

DECLARE @Names varchar(1000)

Select City, @Names = @Names   ', '   Category from B Group by City

CodePudding user response：

I think that you should use GROUP_CONCAT

SELECT B.City, GROUP_CONCAT(C.Category SEPARATOR ', ') as category
FROM Business B
INNER JOIN Category C
ON B.id = C.business_id
GROUP BY B.City;

I hope this helps!

CodePudding user response：

Thanks to The Impaler I referenced the SQLite documentation and used GROUP_CONCAT to combine the results for all the categories in their subsequent cities with the following query:

SELECT B.City As City, GROUP_CONCAT(C.Category, ', ') As Category
FROM Business B
INNER JOIN Category C
ON B.id = C.business_id
GROUP BY B.City