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.

Hmm sounds like you want a string aggregation function. I am not sure which databases that do that natively, but I see somtimes see user-defined functions for that (natuarlly that varies from one dmbs to another). What database are you using? — FrustratedWithFormsDesigner, Sep 17 '10 at 18:08
What errors did `list()` give? Not that I can help with Sybase problems, but someone else who can might recognize the error message. — FrustratedWithFormsDesigner, Sep 17 '10 at 18:57
The error was that `LIST()` function was not found, and rightly so: I'm using Sybase ASE edition, which does not have this function (its documentation says so). This will probably need some T-SQL mumbo jumbo I guess, but I have no idea how to do that.. — jrharshath, Sep 17 '10 at 19:01
The function `LIST()` only works for `Sybase IQ` and not for `Sybase ASE`. — Satyendra, Feb 27 '14 at 14:03

score 5 · Answer 1 · edited Feb 02 '17 at 13:46

5

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

edited Feb 02 '17 at 13:46

Vao Tsun

47,234
13
100
132

answered Sep 17 '10 at 18:07

Konerak

39,272
12
98
118

2

And Oracle has `wmsys.wm_concat`, though it seems to be undocumented. I've never used it myself, but found it pretty quickly. Another Oracle solution using `connect by`: http://halisway.blogspot.com/2006/08/oracle-groupconcat-updated-again.html – FrustratedWithFormsDesigner Sep 17 '10 at 18:12
1

I need to do this with sybase ASE, so this will not work for me... Sybase does have a LIST() function that does this, but it is not present in the ASE edition. – jrharshath Sep 17 '10 at 18:50
my google-fu reveals this link (http://www.projectdmx.com/tsql/rowconcatenate.aspx). Can you convert this to the SQL I'll need in my case? – jrharshath Sep 17 '10 at 20:23

score 2 · Answer 2 · answered Sep 21 '10 at 12:51

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.

answered Sep 21 '10 at 12:51

kolchanov

2,018
2
14
32

score 1 · Answer 3 · edited May 23 '17 at 12:10

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...

score 1 · Answer 4 · answered Sep 17 '10 at 22:27

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.

I'm here having the exact same problem. For me (Sybase ASE 15.0) that doesn't work. For some reason it returns only the last row. Is this a version dependent thing? Or a setting? — Marnix, Mar 17 '12 at 13:31

Bud · Answer 5 · 2015-05-22T14:28:35.203

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

SQL query for "concatenate on join"

5 Answers5