Sybase: How do I concatenate rows in sybase column

Question

I have a table with ID and Dept.

Table is

id  dept    salary  date
1   30      2000    8/25/2015 12:06:54.870 PM
2   20      5500    7/12/2015 12:06:54.870 PM
3   30      6700    11/21/2013 12:06:54.870 PM
4   30      8900    4/16/2009 12:06:54.870 PM
5   30      9900    6/29/2014 12:06:54.870 PM
6   10      1120    7/3/2015 12:06:54.870 PM
7   20      8900    4/13/2013 12:06:54.870 PM
8   10      2400    7/23/2015 12:06:54.870 PM
9   30      2600    8/21/2015 12:06:54.870 PM
10  10      2999    8/3/2015 12:06:54.870 PM

Just need the output like this

Dept  ID
30     1,3,4,5,9

Hello Nigel welcome to StackOverflow, next time try to provide a [**SqlFiddle**](http://sqlfiddle.com/#!15/5368b/6) so we can understand the problem better and give you an answer much faster – Also please read [**How to ask**](http://stackoverflow.com/help/how-to-ask) and [**How to create a Minimal, Complete, and Verifiable example.**](http://stackoverflow.com/help/mcve) — Juan Carlos Oropeza, Aug 26 '15 at 17:59
should work just like this: http://stackoverflow.com/a/1785923/215752 — Hogan, Aug 26 '15 at 18:34
@hogan Q: `Stuff` and `XML Path` are generic tsql? because this questions say sybase. — Juan Carlos Oropeza, Aug 26 '15 at 18:37
@JuanCarlosOropeza - xml support and replace support exist in all modern db platforms. I don't know the details of sybase, so I didn't give an answer instead I made a comment. — Hogan, Aug 26 '15 at 18:38

score 4 · Answer 1 · answered Apr 10 '19 at 17:18

A bit simpler solution for those who want this to work for particular query:

DECLARE @res_csv  VARCHAR(10000)
BEGIN
    SELECT SomeIntField INTO #tmp FROM YourTblName WHERE 1=1 -- a hardcoded query
    UPDATE  #tmp
    SET     @res_csv = @res_csv + case when @res_csv is not NULL then ',' end + CONVERT(VARCHAR, SomeIntField) 
    drop table #tmp
    print @res_csv
END

score 1 · Accepted Answer · answered Aug 27 '15 at 09:30

This is the best way I know. Please do post if anyone knows a better solution:

I have named your table sal

DECLARE @id     INT
        , @max  INT
        , @dep  INT
        , @all  VARCHAR(255)

SELECT  @id = 1
        , @max = MAX(id)
FROM    sal

SELECT * INTO #tmp FROM sal

WHILE (1=1)
BEGIN

    SELECT  @dep = dept
    FROM    #tmp
    WHERE   id = @id

    IF @dep IS NULL
    BEGIN
        SELECT  @id = @id + 1

        IF @id > @max
            BREAK
        ELSE
            CONTINUE
    END

    UPDATE  #tmp
    SET     @all = @all + ',' + CONVERT(VARCHAR, id) 
    WHERE   dept = @dep

    --remove last comma
    select  @all = RIGHT(@all, LEN(@all)-1)

    DELETE  #tmp
    WHERE   dept = @dep

    -- selecting the output. insert into table if you want
    SELECT  @dep, @all

    SELECT  @dep   = NULL
            , @all = NULL

    SELECT  @id = @id + 1

    IF @id > @max
        BREAK

    -- fail safe
    IF @id > 100
        BREAK
END

drop table #tmp

score -2 · Answer 3 · answered Oct 24 '18 at 06:18

-2

I had similar problem and came up with the following solution:

select dept,list(id,',' order by dept) from TableName group by dept

Assuming TableName is the name of your table.

answered Oct 24 '18 at 06:18

Daniel Vaca

159
2
3
15

Sybase: How do I concatenate rows in sybase column

3 Answers3

Linked

Related