Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: Sybase concatenation

    Hello Gurus,

    I have a requirement like below..

    T1 table

    ID code
    001 1
    001 2
    002 1
    003 4
    003 1

    Output should be

    T2 table

    ID code
    001 1,2
    002 1
    003 1,4


    I tried with cursors in Sybase... like below...

    alter proc sp_test_sample
    as
    begin
    DECLARE @k char(8), @id_var char(8), @cd_var VARCHAR(10)

    DECLARE c CURSOR FOR
    select id,code from party_sample order by id,code

    create table #work
    (
    id_work char(8), cd_work varchar(200))

    select @k ="-1"

    OPEN c

    FETCH c INTO @id_var, @cd_var

    while(@@sqlstatus=0)
    begin

    if @id_var != @k
    insert into #work (id_work, cd_work) values (@id_var,@cd_var)
    else
    update #work set cd_work = cd_work+','+@cd_var where id_work = @id_var


    print 'hello %1! , %2!' , @id_var,@cd_var

    select @k = @id_var

    FETCH c INTO @id_var, @cd_var


    end

    CLOSE c

    DEALLOCATE c

    ============


    However as sybase is not a row processor like oracle,.. its taking lot of time.. Could you please suggest an alternative solution using temp tables or something similar which will be faster in Sybase...?

    I am using Sybase IQ.. List() is not a valid command FYI
    Last edited by sandeeppvk; 06-22-11 at 08:11.

  2. #2
    Join Date
    Jun 2011
    Posts
    1
    this should solve your problem...
    select ID as "c_id" , (select list(code) from T1 where ID = "c_id") from T1

  3. #3
    Join Date
    Jun 2011
    Posts
    2
    List is not a valid function in Sybase IQ. Any other options which will be faster for concatenating rows into column?

  4. #4
    Join Date
    May 2011
    Posts
    28

    Sybase concatenation

    Hi,
    Please try this option

    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

  5. #5
    Join Date
    Dec 2009
    Posts
    23
    pradyut.dhara ... your code doesn't appear to work. I too am looking for a solution to this problem.


    Code:
    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
    Results:
    ID CODE
    ---- -------
    001 1
    001 2
    002 1
    003 1
    003 4

    ID (No column name)
    ---- -------
    001 2,2
    002 1,1
    003 4,4
    Last edited by shell_l_d; 03-13-15 at 00:47. Reason: To add results to it
    SQL Server 2008/2010/2012
    Sybase 12.5.4 (has no functions)

  6. #6
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    Hi,

    This is a good question and the fact I see two nearly identical questions makes me think it's some sort of home work question. I have also found an almost exact copy of it in "SQL Cookbook", O'Reilly & Associates, 2006 by Anthony Molinaro. ISBN-10: 0-596-00976-3 ISBN-13: 978-0-596-00976-2 Pages: 121 to 127. I suggest obtaining a copy of this book, though while not directly applicable to Sybase ASE, the methods and discussions provide useful guidance across a number of products.

    1) I'm a little confused over the product you're using. Sybase IQ is mentioned, but Sybase ASE syntax is used in the previous examples

    2) According to the Sybase documentation, Sybase IQ has a a list aggregate function that will do the job.

    http://infocenter.sybase.com/help/in...709667623.html

    Anyway, I have made an attempt at answering this for you, using the tools I have available

    Adaptive Server Enterprise/15.7.0/EBF 19495 SMP /P/x86_64/Enterprise Linux/ase157/2820/64-bit/FBO/Fri Sep 16 00:54:35 2011

    Issues

    1) I noticed there was no unique identifiers for the source table T1, so I have added a row_id column based on an identity column.

    2) Sybase ASE doesn't have
    * a list aggregate function
    * recursive with statement
    * hierarchical connectivity functions
    * windowing functions

    This reduces most of the possibilities that are available with competing products.

    Sybase ASE is not discussed in the book I have mentioned, the solutions presented here are Sybase ASE adaptations of the PostgresSQL solutions which seems to have the same limitations as Sybase ASE. The only reasonable way to achieve the result is using a group by and vector aggregates such as min and max functions.

    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. Assuming the number of entries in each list is know when the query is written, the following can be coded as follows.

    -------
    use tempdb
    go
    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

    select
    id,
    substring(codes, 3, char_length(codes)) as list
    from
    (
    select
    id,
    max(case when pos = 1 then codes else "" end ) +
    max(case when pos = 2 then codes else "" end ) + -- number of cases determined by the list length
    "" as codes
    from
    (
    select
    a.id,
    ", " + a.code as codes,
    (
    select -- Position of the item in the item in the list
    count(*) -- counts the number of items in the "id" less than the current row
    from -- this is executed once per row
    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
    go

    However if the number of items in the list is not known in advance, the SQL needs to be dynamically generated. This is rather simple. Compute the maximum number of items in the list, add the number of case statements and execute the resulting SQL using the execute immediate function...

    -------------------------------------
    use tempdb
    go
    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(16000),
    @mx_positions int,
    @cnt int

    select
    @mx_positions = max(cnt)
    from
    (
    select
    id,
    count(*) as cnt
    from
    T1
    group by
    id
    ) x

    select
    @sql =
    '
    select
    id,
    substring(codes, 3, char_length(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
    '

    exec(@sql)
    go
    ---------------------------

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
  •