SQL query for "concatenate on join"

Question

I'm using a Sybase ASE database.
I have two tables that look like:

Table Shops:

---------------------
| ShopName | ShopID |
---------------------
| Sweetie  | 1      |
| Candie   | 2      |
| Sugarie  | 3      |
---------------------

Table Sweets:

----------------------
| SweetName | ShopID |
----------------------
| lolly     | 1      |
| redlolly  | 1      |
| greenloly | 1      |
| taffy     | 2      |
| redtaffy  | 2      |
| bluetaffy | 2      |
| choco     | 3      |
| mintchoco | 3      |
| milkchoco | 3      |
| gummybees | 3      |
----------------------

I want to write a query that would generate a result that looks like:

-----------------------------------------------------
| ShopName | Sweets                                 |
-----------------------------------------------------
| Sweetie  | lolly, redlolly, greenlolly            |
| Candie   | taffy, redtaffy, bluetaffy             |
| Sugarie  | choco, mintchoco, milkchoco, gummybees |
-----------------------------------------------------

How should I go about doing that? I need this for a Sybase ASE database. I tried the LIST() function, but I'm getting an error on that. I checked its documentation, and turns out, this function is not available in the ASE Edition.

This probably means that there will be some "dynamic sql" involved (I have very little idea what that means). Can anyone help?

I could want ShopId instead of ShopName in the results table... I don't know for sure yet. I guess that won't be much of a difference. Also, trailing commas in Sweets column of results is not an issue. All I want is a non-whitespace separator.

Answer 1

You'll have to specify what DBMS you're using.

MySQL's GROUP CONCAT is exactly what you need.

SELECT ShopName, GROUP_CONCAT(SweetName SEPARATOR ", ")
FROM Shops a
JOIN Sweets b
ON a.ShopID = b.ShopID
GROUP BY ShopName