Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: Help with concatanate..

    Hi Guys,

    I have a table with two concerned columns. One column is id and other is comment. One ID can have multiple comments. I want to concatanate all the comments for a given id in SQL.

    For an Example: if the i/p is
    Code:
    ID   Comment
    1   hai
    1  me
    1  is 
    1  gud
    2  heim
    3  out
    3  of
    I want to have the output as follows :
    Code:
    ID  Comment
    1  hai,me,is,gud
    2  heim
    3  out,of
    Thanks For your help in advance.

    Regards,
    Magesh

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This question gets asked a lot. You need a recursive query to do that. Search this forum for pivot table and you should get examples on how to do this.

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    AFAIK, XMLGROUP would be an easiest way on DB2 for LUW 9.5 or later.

    Search this forum with "XMLGROUP concatenate strings".

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb LEFT JOIN TABLE, only

    The Problem could be solved with LEFT JOIN TABLE, only.

    In this case we have to know the maximum number occurrences for ID in YOUR_TABLE, for example 5:


    Code:
    with Your_table(ID, Comment) as
    (
    select 1, 'hai'   from sysibm.sysdummy1 union all
    select 1,  'me'   from sysibm.sysdummy1 union all
    select 1,  'is'   from sysibm.sysdummy1 union all
    select 1,  'gud'  from sysibm.sysdummy1 union all
    select 2,  'heim' from sysibm.sysdummy1 union all
    select 3,  'out'  from sysibm.sysdummy1 union all
    select 3,  'of'   from sysibm.sysdummy1 
    ) 
    select t1.id, 
    t1.Comment1 
    || ifnull(', ' || t2.Comment2, '')
    || ifnull(', ' || t3.Comment3, '')
    || ifnull(', ' || t4.Comment4, '')
    || ifnull(', ' || t5.Comment5, '') 
    as Comments
    
    From
    (select id, min(Comment) Comment1 
      from Your_table
     group by id ) t1
    
    left join table
    (select i2.id, min(i2.Comment) Comment2 
      from Your_table i2 
     where t1.id = i2.id
       and i2.Comment > t1.Comment1
     group by id ) t2
    on t1.id = t2.id
    
    left join table
    (select i3.id, min(i3.Comment) Comment3 
      from Your_table i3 
     where t1.id = i3.id
       and i3.Comment > t2.Comment2
     group by id ) t3
    on t1.id = t3.id
    
    left join table
    (select i4.id, min(i4.Comment) Comment4 
      from Your_table i4 
     where t1.id = i4.id
       and i4.Comment > t3.Comment3
     group by id ) t4
    on t1.id = t4.id
    
    left join table
    (select i5.id, min(i5.Comment) Comment5 
      from Your_table i5 
     where t1.id = i5.id
       and i5.Comment > t4.Comment4
     group by id ) t5
    on t1.id = t5.id
    Result:

    ID.......... COMMENTS
    1........... gud, hai, is, me
    2........... heim
    3........... of, out
    Lenny

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Even much easier

    We can also simplify the query:

    Code:
    with Your_table(ID, Comment) as
    (
    select 1, 'hai'   from sysibm.sysdummy1 union all
    select 1,  'me'   from sysibm.sysdummy1 union all
    select 1,  'is'   from sysibm.sysdummy1 union all
    select 1,  'gud'  from sysibm.sysdummy1 union all
    select 2,  'heim' from sysibm.sysdummy1 union all
    select 3,  'out'  from sysibm.sysdummy1 union all
    select 3,  'of'   from sysibm.sysdummy1 
    ) 
    select t1.id, 
    t1.Comment 
    || ifnull(', ' || t2.Comment, '')
    || ifnull(', ' || t3.Comment, '')
    || ifnull(', ' || t4.Comment, '')
    || ifnull(', ' || t5.Comment, '') 
    as Comments
    
    From
    (select id, min(Comment) Comment
      from Your_table
     group by id ) t1
    
    left join table
    (select t1.id, min(i2.Comment) Comment 
      from Your_table i2 
     where t1.id = i2.id
       and i2.Comment > t1.Comment) t2
    on 1 = 1
    
    left join table
    (select t1.id, min(i3.Comment) Comment 
      from Your_table i3 
     where t1.id = i3.id
       and i3.Comment > t2.Comment) t3
    on 1 = 1
    
    left join table
    (select t1.id, min(i4.Comment) Comment 
      from Your_table i4 
     where t1.id = i4.id
       and i4.Comment > t3.Comment) t4
    on 1 = 1
    
    left join table
    (select t1.id, min(i5.Comment) Comment 
      from Your_table i5 
     where t1.id = i5.id
       and i5.Comment > t4.Comment) t5
    on 1 = 1
    Result:

    ID.......... COMMENTS
    1........... gud, hai, is, me
    2........... heim
    3........... of, out
    Lenny

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    but Lenny this solution is only good if you have 4 or less comments. The other options posted would be best in a case like this, unless you have a rule that there will only ever be a max of N.
    Dave

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Any number of JOIN

    Quote Originally Posted by dav1mo View Post
    but Lenny this solution is only good if you have 4 or less comments. The other options posted would be best in a case like this, unless you have a rule that there will only ever be a max of N.
    Dave
    You can add any number of LEFT JOINs together with concat in SELECT part.
    Next example with 7 JOINs with same result:


    Code:
    with Your_table(ID, Comment) as
    (
    select 1, 'hai'   from sysibm.sysdummy1 union all
    select 1,  'me'   from sysibm.sysdummy1 union all
    select 1,  'is'   from sysibm.sysdummy1 union all
    select 1,  'gud'  from sysibm.sysdummy1 union all
    select 2,  'heim' from sysibm.sysdummy1 union all
    select 3,  'out'  from sysibm.sysdummy1 union all
    select 3,  'of'   from sysibm.sysdummy1 
    ) 
    select t1.id, 
    t1.Comment 
    || ifnull(', ' || t2.Comment, '')
    || ifnull(', ' || t3.Comment, '')
    || ifnull(', ' || t4.Comment, '')
    || ifnull(', ' || t5.Comment, '') 
    || ifnull(', ' || t6.Comment, '')
    || ifnull(', ' || t7.Comment, '')  
    as Comments
    
    From
    (select id, min(Comment) Comment
      from Your_table
     group by id ) t1
    
    left join table
    (select t1.id, min(i2.Comment) Comment 
      from Your_table i2 
     where t1.id = i2.id
       and i2.Comment > t1.Comment) t2
    on 1 = 1
    
    left join table
    (select t1.id, min(i3.Comment) Comment 
      from Your_table i3 
     where t1.id = i3.id
       and i3.Comment > t2.Comment) t3
    on 1 = 1
    
    left join table
    (select t1.id, min(i4.Comment) Comment 
      from Your_table i4 
     where t1.id = i4.id
       and i4.Comment > t3.Comment) t4
    on 1 = 1
    
    left join table
    (select t1.id, min(i5.Comment) Comment 
      from Your_table i5 
     where t1.id = i5.id
       and i5.Comment > t4.Comment) t5
    on 1 = 1
    
    left join table
    (select t1.id, min(i6.Comment) Comment 
      from Your_table i6 
     where t1.id = i6.id
       and i6.Comment > t5.Comment) t6
    on 1 = 1
    
    left join table
    (select t1.id, min(i7.Comment) Comment 
      from Your_table i7 
     where t1.id = i7.id
       and i7.Comment > t6.Comment) t7
    on 1 = 1
    ID.......... COMMENTS
    1........... gud, hai, is, me
    2........... heim
    3........... of, out
    Lenny

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    but that is the problem I spoke of. You have to know how many outer joins to put into the statement and that is why it is not the best option. a single id could have N comments. How do you know how many outer joins to put in? THat is why recursion is the best option. Otherwise you would have to write your SQL with ever increasing number of outer joins and then how would you know when you had exceeded the outer joins that you had written.
    Dave

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by dav1mo View Post
    but that is the problem I spoke of. You have to know how many outer joins to put into the statement and that is why it is not the best option. a single id could have N comments. How do you know how many outer joins to put in? THat is why recursion is the best option. Otherwise you would have to write your SQL with ever increasing number of outer joins and then how would you know when you had exceeded the outer joins that you had written.
    Dave
    I shown recursion for this many times. Now I shown another way. Why not.
    In dynamic SQL you can add N + 1 JOIN without any problem.

    You don't need to rename inside tables with I1 -- In. You can use I1 for all of them and that it.

    Lenny

Posting Permissions

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