Sybase：如何连接sybase列中的行

小编典典

Sybase：如何连接sybase列中的行

sql

我有一个ID和部门表。

表是

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

只需要这样的输出

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

阅读 158

2021-04-07

共1个答案

小编典典

这是我所知道的最好方法。如果有人知道更好的解决方案，请发帖：

我已经把你的桌子命名了 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

2021-04-07