Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    23

    Question Unanswered: Sybase RowConcatenation - without cursor (too slow) & functions (none in our version)

    Hi

    I'm trying to concatenate values from multiple rows in a table in Sybase (v12.5 I think) without using a cursor. Can't use the list function as our version of Sybase has NO functions. The Cursor is extremely slow hence need another way of performing this please.

    Here is a basic version of what I'm after (my scenario has 2 id fields not just 1). Any help would be appreciated thanks.

    Source:
    Code:
    GroupID   Txt
    ----------   ------
    1              A1
    1              A2
    1              A3
    2              A4
    2              A5
    3              A6
    3              A7
    3              A8
    Results Required:
    Code:
    GroupID    Txt
    ----------   ------
    1              A1,A2,A3
    2              A4,A5
    3              A6,A7,A8
    Here is some code I have tried already, but it only works in SQL Server NOT in the Sybase that we have:

    Code:
    CREATE TABLE #T_ConcatDemo (
        GroupId   INT 
       ,Txt       VARCHAR(5)
    ) 
    
    INSERT INTO #T_ConcatDemo 
    (GroupId, Txt)
          SELECT 1,'A1'
    UNION SELECT 1,'A2'
    UNION SELECT 1,'A3'
    UNION SELECT 2,'A4'
    UNION SELECT 2,'A5'
    UNION SELECT 3,'A6'
    UNION SELECT 3,'A7'
    UNION SELECT 3,'A8' 
    
    SELECT * FROM #T_ConcatDemo
    
    
    -- SqlServer: works but not grouped
    -- SqlServer=YES, Sybase=NO
    DECLARE @ConcatCodes1a VARCHAR(100) 
    SET @ConcatCodes1a = ''
    SELECT @ConcatCodes1a += Txt + ',' FROM #T_ConcatDemo
    SELECT @ConcatCodes1a as ConcatCodes1a
    
    
    -- Sybase: only stores the last value: ',A8'
    -- SqlServer: works but not grouped (& has trailing comma)
    -- SqlServer=YES, Sybase=NO
    DECLARE @codes VARCHAR(100)
    SELECT @codes = COALESCE(@codes + ',','') + Txt FROM #T_ConcatDemo
    SELECT @codes AS ConcatCodes1b
    
    
    /*
    -- Sybase Error: List(..) function list not found (our version does not have functions)
    -- SqlServer=NO, Sybase=NO
    select GroupId, LIST(Txt,',') AS ConcatCodes2
    from #T_ConcatDemo
    group by GroupId
    */
    
    -- SqlServer: works but not grouped
    -- SqlServer=YES, Sybase=NO
    SELECT LEFT(C, LEN(C) - 1) as ConcatCodes3
    FROM (
          SELECT RTRIM(Txt) + ',' 
          FROM #T_ConcatDemo
          FOR XML PATH('')
         ) AS D(C) 
    
    
    -- Sybase: incorrect syntax near PATH
    -- Sybase error: incorrect syntax near the keyword 'FOR XML'
    -- SqlServer=YES, Sybase=NO
    Select A.GroupId
          ,(Select STUFF( (Select ',' + RTRIM(Txt)
                           from #T_ConcatDemo 
                           where GroupId = A.GroupId
                           FOR XML PATH('')
                          ),1,1,'') AS cols
           ) as ConcatCodes4
    from #T_ConcatDemo as A 
    group by A.GroupId
    
    
    drop table #T_ConcatDemo
    
    
    /*
    -- Source: http://www.dbforums.com/showthread.php?1667557-Sybase-concatenation
    -- Doesn't work unfortunately
    
    CREATE TABLE T1 (
         ID         VARCHAR(5)
        ,CODE       VARCHAR(12)
    )
    
    INSERT INTO T1 (ID, CODE)
    SELECT '001', 1 UNION
    SELECT '001', 2 UNION
    SELECT '002', 1 UNION
    SELECT '003', 1 UNION
    SELECT '003', 4
    
    SELECT * FROM T1
    
    DECLARE @LastID Varchar(3)
           ,@LastCode VARCHAR(12)
    
    SELECT ID, CODE, CONVERT(VARCHAR(25),'') AS RESULT 
    INTO #T1 
    FROM T1 
    ORDER BY ID, CODE
    
    UPDATE #T1
    SET RESULT    = CASE WHEN ID = @LastID THEN @LastCode + ',' + CODE
                         ELSE CODE
                         END
       ,@LastID   = ID
       ,@LastCode = CODE 
    SELECT ID
          ,MAX(RESULT) 
    FROM #T1 
    GROUP BY ID
    GO 
    
    DROP TABLE T1
    DROP TABLE #T1
    */
    Last edited by shell_l_d; 03-13-15 at 03:01. Reason: code/formatting
    SQL Server 2008/2010/2012
    Sybase 12.5.4 (has no functions)

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

  3. #3
    Join Date
    Dec 2009
    Posts
    23
    Thanks richardcrossley

    This code now works in MS SQL Server (our development environment) & Sybase 12.5 (our production environment). We are moving to SQL server soon, so need it to work for both.

    I don't need them to be sorted in order & also have to add a further id column, eg: id1, id2, code.

    I'll give it a go.

    Code:
    -- source: http://www.dbforums.com/showthread.php?1667557-Sybase-concatenation&p=6632010#post6632010
    --
    -- if the number of items in the list is not known in advance, the SQL needs to be dynamically generated. 
    -- Compute max number of items in the list, add the number of case statements and execute the resulting SQL 
    -- using the execute immediate function.
    --
    -- The interesting part of the SQL is the subquery returning the value for pos. This queries the number of 
    -- rows for the id. To ensure an ordered list and each element having a position, there must be a unique 
    -- identifier for each row in T1.
    --
    -- The next outer query gathers these results and formats the list. The max(case) statements find the relevant 
    -- code based on the position and then concatenate the codes together as they are iterated over.
    --
    -- The final outer query tidies up the list format by removing the leading ', '
    --
    -- The result set is the id and a list of code values.
    
    set nocount on
    go
    
    if object_id('T1') is not null
    begin
        drop table T1
    end 
    go
    
    create table T1 (
        row_id      int             identity,
        id          varchar(5)      not null,
        code        varchar(12)     not null
    )
    go
    
    insert into T1 ( id, code )
    SELECT '001', '1' UNION
    SELECT '001', '2' UNION
    SELECT '002', '1' UNION
    SELECT '003', '4' UNION
    SELECT '003', '1'
    go
    
    
    declare @sql                varchar(8000)
           ,@mx_positions       int
           ,@cnt                int
    
    select @mx_positions = max(cnt)
    from (
            select id,
                   count(*) as cnt
            from T1
            group by id 
         ) x
    
    -- char_length for sybase
    select @sql = 'select id ' + 
                        ',substring(codes, 3, len(codes)) as list ' + 
                  'from (select id,' 
    
    select @cnt = 1
    while @cnt <= @mx_positions
    begin
        select @sql = @sql +  'max(case when pos = ' + convert(varchar, @cnt) + ' then codes else '''' end ) + ' + char(10)
        select @cnt = @cnt + 1
    end 
    
    select @sql = @sql +      ' '''' as codes ' + 
                        'from (select a.id, ' + 
                                   ''', '' + a.code as codes ' +
                                    ',(select count(*) ' +
                                      'from T1 b ' +
                                      'where a.id = b.id ' +
                                        'and b.row_id <= a.row_id ' +
                                     ') as pos ' +
                              'from T1 a ' +
                             ') x ' +
                        'group by id ' +
                       ') y ' +
                  'order by id'
    
    print @sql
    exec(@sql)
    
    if object_id('T1') is not null
    begin
        drop table T1
    end 
    go
    SQL Server 2008/2010/2012
    Sybase 12.5.4 (has no functions)

  4. #4
    Join Date
    Dec 2009
    Posts
    23
    Modified code to work with 2 id's & a code

    Code:
    -- source: http://www.dbforums.com/showthread.php?1667557-Sybase-concatenation&p=6632010#post6632010
    --
    -- if the number of items in the list is not known in advance, the SQL needs to be dynamically generated. 
    -- Compute max number of items in the list, add the number of case statements and execute the resulting SQL 
    -- using the execute immediate function.
    --
    -- The interesting part of the SQL is the subquery returning the value for pos. This queries the number of 
    -- rows for the id. To ensure an ordered list and each element having a position, there must be a unique 
    -- identifier for each row in T1.
    --
    -- The next outer query gathers these results and formats the list. The max(case) statements find the relevant 
    -- code based on the position and then concatenate the codes together as they are iterated over.
    --
    -- The final outer query tidies up the list format by removing the leading ', '
    --
    -- The result set is the id and a list of code values.
    
    set nocount on
    go
    
    if object_id('T1') is not null
    begin
        drop table T1
    end 
    go
    
    create table T1 (
        row_id      int             identity,
        id          varchar(5)      not null,
        id2         int             not null,
        code        varchar(12)     not null
    )
    go
    
    insert into T1 ( id, id2, code )
    SELECT '123', 30, '1' UNION
    SELECT '123', 30, '2' UNION
    SELECT '123', 16, '1' UNION
    SELECT '123', 16, '2' UNION
    SELECT '128', 42, '1' UNION
    SELECT '145', 23, '4' UNION
    SELECT '145', 23, '1' UNION
    SELECT '145', 55, '1'
    go
    
    select * from T1
    
    declare @sql                varchar(8000)
           ,@mx_positions       int
           ,@cnt                int
    
    select @mx_positions = max(cnt)
    from (
            select id
                  ,id2
                  ,count(*) as cnt
            from T1
            group by id 
                    ,id2
         ) x
    
    -- char_length for sybase
    select @sql = 'select id ' + 
                        ',id2 ' + 
                        ',substring(codes, 3, len(codes)) as list ' + 
                  'from (select id,id2,' 
    
    select @cnt = 1
    while @cnt <= @mx_positions
    begin
        select @sql = @sql +  'max(case when pos = ' + convert(varchar, @cnt) + ' then codes else '''' end ) + ' + char(10)
        select @cnt = @cnt + 1
    end 
    
    select @sql = @sql +      ' '''' as codes ' + 
                        'from (select a.id, a.id2, ' + 
                                   ''', '' + a.code as codes ' +
                                    ',(select count(*) ' +
                                      'from T1 b ' +
                                      'where a.id  = b.id ' +
                                        'and a.id2 = b.id2 ' +
                                        'and b.row_id <= a.row_id ' +
                                     ') as pos ' +
                              'from T1 a ' +
                             ') x ' +
                        'group by id, id2 ' +
                       ') y ' +
                  'order by id, id2'
    
    print @sql
    exec(@sql)
    
    if object_id('T1') is not null
    begin
        drop table T1
    end 
    go
    SQL Server 2008/2010/2012
    Sybase 12.5.4 (has no functions)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •