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

Answer 2

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.

Answer 3

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 4

Unfortunately, a method in adrift's answer don't work with select statement for Sybase ASE, variable @list don't update for each row, it works only for last row. But because update perform for each row and table's size don't to large, you could do it with update statement. Small example:

    declare @list varchar(500)

    update Sweets
    set @list = @list + SweetName + ', ' 
    where ShopID = 1

    select SUBSTRING(@list, 1, Len(@list) - 2)

P.S. As for me, cursor isn't good way...

Answer 5

Works in Sybase ASE...

CREATE FUNCTION SweetsList(@SN varchar(10))
returns varchar(255)
AS
DECLARE @SwNList varchar(255)
DECLARE @FetchSwN varchar(55)
DECLARE @Status INT, @Error INT

DECLARE ListCurs CURSOR FOR
SELECT SweetName
  FROM Sweets AS SW
 JOIN Shops AS SH
  ON SH.ShopID = SW.ShopID
 WHERE SH.ShopName = @SN
FOR READ ONLY

OPEN ListCurs
SELECT @Status = 0
WHILE @Status = 0
BEGIN
    FETCH ListCurs INTO @FetchSwN

    SELECT @Status = @@SQLSTATUS

    IF @Status = 0 
    BEGIN
       SELECT  @SwNList = CASE WHEN @SwNList IS NULL THEN '' ELSE @SwNList + ', ' END + @FetchSwN
    END
END
CLOSE ListCurs
RETURN (@SwNList)
go

Then execute ...

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