How to combine resulting rows into a single row seperated by commas

Question

I've been trying to combine my query results into a single row with each set of tag and associatedid values separated by a comma.

Using the following values:

tag         associatedid
MA2D8111AM  MA2D811S
MA2D8112AM  MA2D811S
MA2D8113AM  MA2D811S
MA2D8114AM  MA2D811S
MA2D8115AM  MA2D811S
MA2D8116AM  MA2D811S
MA2D8117AM  MA2D811S
MA2D8118AM  MA2D811S
MA2D8119AM  MA2D811S

I would like to select these set of values in such a way that they would be displayed as followed:

MA2D8111AM:MA2D811S,MA2D8112AM:MA2D811S,MA2D8113AM:MA2D811S,MA2D8114AM:MA2D811S,MA2D8115AM:MA2D811S,MA2D8116AM:MA2D811S,MA2D8117AM:MA2D811S,MA2D8118AM:MA2D811S,MA2D8119AM:MA2D811S

I have managed to combine the two columns with ":" using the following, but honestly have no idea how to combine all the set of tags and associatedid on the same row:

SELECT tag+":"+associatedid FROM tagidtable

When reading through sql_server examples, I have not been able to replicate user's successes when applying COALESCE in sybase. Any tips or feedback would be much appreciated.

What version of sybase? Does it support the "LIST" Function? http://stackoverflow.com/questions/17094656/sql-script-to-pull-duplicate-data http://dcx.sybase.com/1200/en/dbreference/list-function.html — xQbert, Sep 24 '14 at 19:53
Concatenating strings from rows into a single string is not an easy thing to do in SQL. You usually have to write some sort of function that loops through the results with a cursor. Would it be possible to do it in the application/reporting layer instead? — D Stanley, Sep 24 '14 at 19:54
Not easy? For XML(SQL_SERVER), WM_CONCAT (ORACLE < 10g), LIST_AGG (ORACLE), Group_Concat(MySQL), LIST (SQLAnywhere-Sybase) seems easy to me... but if it's not one of these RDBMS we just need to find the similar function and if not then build... — xQbert, Sep 24 '14 at 19:55

score 1 · Answer 1 · answered Sep 24 '14 at 19:53

1

This is going to work on a modern version of SQL server, but I have my doubts as to whether sybase has the same syntax and functionality around "FOR XML"

SELECT SUBSTRING(
    (SELECT  ', ' + RTRIM(tag+':'+associatedid) [text()]
     FROM tagidtable  
     FOR XML PATH('') 
    ), 2, 1000000000)

answered Sep 24 '14 at 19:53

bms

11
1

Unfortunately XML cannot be read on our version of sybase, it's one of those sad moments when there are thousands of solutions...but for another sql version – user3166881 Sep 24 '14 at 19:56

score 1 · Accepted Answer · answered Sep 24 '14 at 22:29

I'm not familiar with what's available in sybase but I assume you can do some sort of while loop. This should get you started, I would convert this into a function to be reusable. I didn't put this in a cursor which would just allow you to fetch next, instead I just put 1,2,3,4 but I just wanted to illustrate the idea without providing the exact code :-)

CREATE TABLE ConvertRowsToSingleCell
(
    sequence int
    ,tag varchar(max)
    ,associatedId varchar(max)
)

INSERT INTO ConvertRowsToSingleCell
VALUES 
    (1,'MA2D8111AM', 'MA2D811S')
    ,(2, 'MA2D8112AM', 'MA2D811S')
    ,(3, 'MA2D8113AM', 'MA2D811S')
    ,(4, 'MA2D8114AM', 'MA2D811S')

DECLARE @Length int = (Select COUNT(*) FROM ConvertRowsToSingleCell)
        ,@Increment int = 1
        ,@Item varchar(max)
        ,@ConcatenatedString varchar(max);

WHILE @Increment <= @Length
BEGIN

    IF (@Increment = @Length) --Remove comma at end
        BEGIN
            Select @Item = tag + ':' + associatedId from ConvertRowsToSingleCell
            where @Increment = sequence
        END
    ELSE
        BEGIN
            Select @Item = tag + ':' + associatedId + ', ' from ConvertRowsToSingleCell
            where @Increment = sequence
        END
    Set @ConcatenatedString = ISNULL(@ConcatenatedString,'') + @Item
    SET @Increment = @Increment + 1 
END

Select @ConcatenatedString

How to combine resulting rows into a single row seperated by commas

2 Answers2