Sybase: How do I concatenate rows in sybase column

问题:

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

回答1:

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

回答2:

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

回答3:

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.