Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    42

    Unanswered: how can i finish the query?

    Hi,

    I have this sample table in Sybase DB:
    Table T1:
    col1 col2
    ---------------------------------
    a1 x1
    a1 x2
    a1 x3
    a2 y1
    a2 y2
    a2 y3

    My desired output is :

    -------------------------------
    a1 x1x2x3
    a2 y1y2y3

    The second column has the rowvalues for each col1 concatenated.
    col1 and col2 are char(2) fields.

  2. #2
    Join Date
    Nov 2003
    Posts
    2

    Smile Re: how can i finish the query?

    You need to use LIST.

    This should work (it did for me anyhow!)


    SELECT col1, (SELECT LIST (col2, '') FROM T1 TB2 WHERE TB1.col1 = TB2.col1)AS strList
    FROM T1 TB1


    Regards,

    Yahoo!

  3. #3
    Join Date
    Nov 2003
    Posts
    42
    it gives me a wrong message:
    cannot run this command because java services are not enabled.

  4. #4
    Join Date
    Sep 2003
    Posts
    17
    Hi,

    I would use cursors.

    TEST TABLE (content and structure)
    ==========

    1> select * from mytest
    2> go
    col1 col2 col3
    ----------- ----------- -----------
    1 2 3
    1 2 3
    1 22 33
    1 42 43
    3 2 3
    3 5 3
    0 2 3
    5 22 43
    5 32 3

    (9 rows affected)
    1> sp_help mytest
    2> go
    Name Owner Type
    ------------------------------ ------------------------------ ----------------------
    mytest dbo user table

    (1 row affected)
    Data_located_on_segment When_created
    ------------------------------ --------------------------
    default Oct 30 2003 11:20AM
    Column_name Type Length Prec Scale Nulls Default_name Rule_name Identity
    --------------- --------------- ------ ---- ----- ----- --------------- --------------- --------
    col1 int 4 NULL NULL 0 NULL NULL 0
    col2 int 4 NULL NULL 0 NULL NULL 0
    col3 int 4 NULL NULL 0 NULL NULL 0
    Object does not have any indexes.
    No defined keys for this object.
    Object is not partitioned.
    Lock scheme Allpages
    The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme.
    The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme.

    exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
    ------------ -------------- ---------- ----------------- ------------
    1 0 0 0 0
    concurrency_opt_threshold
    -------------------------
    0
    (return status = 0)
    1>
    =================================
    use mypdb
    go

    drop table finaltest
    go
    create table finaltest (col1 int, col2 varchar(255), col3 varchar(255))
    go

    declare concat_cur cursor for
    select col1, col2, col3
    from mytest
    order by col1
    for read only
    go

    declare @col1 int, @col2 int, @col3 int
    declare @current_col1 int
    declare @col2List varchar(255)
    declare @col3List varchar(255)

    open concat_cur
    fetch concat_cur into @col1, @col2, @col3

    select @current_col1 = @col1

    while (@@sqlstatus = 0)
    begin
    if (@current_col1 = @col1)
    begin
    select @col2List = case (@col2List) when NULL then convert(varchar,@col2)
    else @col2List + "," + convert(varchar,@col2)
    end
    select @col3List = case (@col3List) when NULL then convert(varchar,@col3)
    else @col3List + "," + convert(varchar,@col3)
    end
    end
    else
    begin
    insert into finaltest values (@current_col1, @col2List, @col3List)
    select @current_col1 = @col1
    select @col2List = ""
    select @col3List = ""
    select @col2List = convert(varchar,@col2)
    select @col3List = convert(varchar,@col3)
    end

    fetch concat_cur into @col1, @col2, @col3
    end
    insert into finaltest values (@current_col1, @col2List, @col3List)
    go

    select * from finaltest
    order by col1
    go
    =====================
    RESULT:

    0 2 3
    1 2,2,22,42 3,3,33,43
    3 2,5 3,3
    5 22,32 43,3

    I hope it helps.

    Regards,
    Ulrike

  5. #5
    Join Date
    Oct 2003
    Location
    Abu Dhabi
    Posts
    14
    Its better to use table instances if table is not big oterwise its effect the performance

    try the below sql as you mention above

    select T1.COL1,T1.COL2 + T2.COL2+T3.COL2
    from
    TAB T1,TAB T2,TAB T3
    WHERE
    T1.COL1 = T2.COL1 AND
    T1.COL1 = T3.COL1

Posting Permissions

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