SQL query for "concatenate on join"

Question

I am using the Sybase ASE database .
I have two tables that look like this:

Shops table:

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

Sweets table:

 ---------------------- | 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 will generate a result that looks like this:

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

How can I do it? I need this for a Sybase ASE database. I tried the LIST() function, but I am getting an error. I checked its documentation and it turns out this feature is not available in ASE Edition.

This probably means that some kind of "dynamic sql" will be involved (I do not quite understand what this means). Can anyone help?

I might want ShopId instead of ShopName in the result table ... I still don't know for sure. I think it will not matter much. In addition, trailing commas in the Sweets column of results are not a problem. All I want is a separator without spaces.

Answer 1

You need to indicate which DBMS you are using.

MySQL 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

This is a crosstab query, and this is not possible with Sybase ASE in a single query.

You can create a stored procedure with a temporary table, fill it with the 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, perhaps this will allow you to come closer to solve the problem.

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 

Then I can execute this query and get the desired results:

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

Hope this helps.

Answer 4

Unfortunately, the method in the adrift response does not work with the select statement for Sybase ASE, the @list variable is not updated for each line, it only works for the last line. But since updating is performed for each row and table size, it is not large, you can do this with the update statement. A small example:

  declare @list varchar(500) update Sweets set @list = @list + SweetName + ', ' where ShopID = 1 select SUBSTRING(@list, 1, Len(@list) - 2) 

PS As for me, the cursor is not a good way ...

Answer 5

Powered by 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 do ...

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