Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    104

    Unanswered: Help with concatenate query

    Hello,

    Can you help me here.

    I have data like

    col1 col2 col3
    a 1 abc
    b 1 def
    c 2 bhj
    a 1 xyz
    b 2 d123
    b 1 b123

    I want resultset as

    a 1 abc+xyz
    b 1 def+b123
    b 2 d123
    c 2 bhj

    Basically , concatenate col3 grouped by col1 and col2. col3 is of char datatype.

    Any help on this will be apprciated,

    Thanx a ton for your help in advance,

    --Pooja

  2. #2
    Join Date
    Dec 2005
    Posts
    39

    Concatenate query

    is the data resident in the same table or in different tables? if in the same table you might have to make use of self join which could potentially return more than what is expected. to get the results in concatenated form you can use col1 + " + " + col2 (if you need + as well need to include that in quotes). hope this helps.

  3. #3
    Join Date
    Dec 2002
    Posts
    104
    Thanx for your reply,

    problem here is if there are 2 rows then its fine . but if there are more than 2 rows, this does not works that well.

  4. #4
    Join Date
    Dec 2005
    Posts
    39
    i agree, how much levy do you have to split the data into multiple tables. is it possible? i tried most possibilities till now but couldn't find any suitable method as yet. Tried temp table, distinct one or the other leads to cartesian products which is not what we want. Writing a stored procedure is the only thing which i havent tried yet, maybe i would if i find time.

  5. #5
    Join Date
    Dec 2002
    Posts
    104
    Hi , I am writing the code so i have option to use n number of temp table (provided with gud performance) , but i would definately like to avoid cursors here.

  6. #6
    Join Date
    Dec 2002
    Posts
    104
    This is the solution which is working for my need.

    Converted the data into (rownumber grouped by id)
    col1 col2 rownumb
    id a 1
    id b 2
    id c 3
    id1 d 1
    id1 e 2
    id2 a 1
    id3 b 1

    then

    declare @cnt int , @max_cnt int
    select @cnt=2, @max_cnt= max(rownumb)
    from #b

    while (@cnt <= @max_cnt)
    begin

    update #b
    set a2 = ltrim(T1.a2) + ltrim(T2.a2)
    from #b T1, #b T2
    where T1.a1 = T2.a1
    and T1.a2 != T2.a2
    and T1.a3 = 1
    and T2.a3 = @cnt

    select @cnt = @cnt+1

    end

    Let me know if you see some flaw with it...

    Thanx alot for your help

    --Pooja

  7. #7
    Join Date
    Dec 2005
    Posts
    39
    i thought the initial data was something of this format
    x1 x2 x3
    a 1 abc
    b 1 def
    c 2 bhj
    a 1 xyz
    b 2 d123
    b 1 b123

    so if we generate rownumber based on the above it would come out like
    a 2
    b 3
    c 1
    i.e. count of x1. Now with this approach the loop below won't satisfy your requirement i think. can you tell me how are you generating sample data set & correspondingly row number from data.

    Since you're using rowid like operation have you taken into account holes in data (i mean if some of the rows are deleted, would the loop still work and give desired results). Honestly i can tell only if i know how the data is generated. But if it works for you then i don't see any problem.

  8. #8
    Join Date
    Dec 2002
    Posts
    104
    Hey,

    Yeah you r right i am using rowid kind of functionality in sybase. Regarding holes in the data, this data will be build everyday morning (batch program).So, does not have to worry about deletion of data.

    Also , i have tried this logic till 16 level and it is able to concatenate data till that level. So, i think its gonna work.

    To generate rowid , i am using this query,
    -------------------SQL Start ------------------
    create table #b (a1 char(4), a2 varchar(10))
    insert into #b values ("id","A")
    insert into #b values ("id","B")
    insert into #b values ("id","C")
    insert into #b values ("id1","D")
    insert into #b values ("id1","E")
    insert into #b values ("id1","F")
    insert into #b values ("id1","F1")
    insert into #b values ("id2","G")
    insert into #b values ("id2","H")

    select a1,a2,rownumb =
    (select count(*) from #b t2
    where t2.a1=t1.a1
    --more joins can be added acc. to unique key, here col. a1 is a key
    and t2.a2 <= t1.a2)
    from #b t1
    order by a1,a2
    ----------------- SQL end ---------------

    Thanx alot for your help,
    --Pooja

Posting Permissions

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