[SYBASE] SQL query for "concatenate on join"

Question

NOTE: I'm still looking for an answer that I can accept.

---

Hi,

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.

Thanks,
jrh

Answer 1

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

MySQL's GROUP CONCAT is exactly what you need.

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

Answer 2

I tested this on SQL Server, but hopefully it will also work on Sybase. If not, maybe it will get you near enough to solve it.

If I create this function:

CREATE FUNCTION SweetsList(@shopID int)
RETURNS varchar(500)
AS
BEGIN

    DECLARE @list varchar(500)

    SELECT @list = COALESCE(@list+', ','') + SweetName
    FROM Sweets
    WHERE ShopID = @shopID

    RETURN @list
END

I can then execute this query and get the results you want:

SELECT ShopName, dbo.SweetsList(ShopID) AS Sweets
FROM Shops

Hope this helps.

Answer 3

It is a crosstab query and it is impossible with Sybase ASE in one query.

You can create a stored procedure with temporary table, fill it with cursor, and select from this temporary table.