How can I combine comment fields into a single row while maintaining the key?

Hello,

Can anyone suggest a query method of combining comment fields from multiple rows into a single row while maintaining the key field. Please see example data in attachment.

I have limited access to an older AS400 database and I'm looking for a method to combine these comment fileds using a SELECT statement or nested SELECT statements. I need to maintain the unique Case Number while concatenating the comments into a single field.

Thanks,
David

 data set example
dking_wriAsked:
Who is Participating?
 
Dave FordConnect With a Mentor Software Developer / Database AdministratorCommented:

One of the other experts will probably post some really cool recursive SQL, but the following function will probably work for you.

HTH,
DaveSlash

select *       
  from deleteme

CASENUMBER      SEQUENCNBR   COMMENT         
         1               1   This is         
         1               2   a three-        
         1               3   line comment    
         2               1   Here is a       
         2               2   two-line comment

Create Function GetCommentList (@CaseNum Int) 
  Returns VarChar(1024)
  Language SQL
  Reads SQL Data
  Set Option Commit=*None
Begin
    Declare @List VarChar(1024) Not Null Default '';
    Declare @i    Int           Not Null Default 0; 
                                                    
    For CommentList AS Comments Cursor For          
    Select Comment
    From   deleteme
    Where  caseNumber=@CaseNum
    Do
       Set @List=@List||Comment;
    End For;
    Return @List;
End

select distinct
       caseNumber,
       GetCommentList(caseNumber)
  from deleteme
 order by caseNumber

CASENUMBER   GETCOMMENTLIST             
         1   This isa three-line comment
         2   Here is atwo-line comment

Open in new window

0
 
momi_sabagCommented:
for older version you can not use recursive sql
you will have to write a function
0
 
dking_wriAuthor Commented:
Dave,

THanks for the function. I'm going to get with IT and see if they can create this for me on the server. I'll let you know how it goes.
0
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:

Speaking of recursive SQL.  :)

  http://www.experts-exchange.com/Database/DB2/A_3629-Recursive-SQL-in-DB2-Converting-Columns-to-Rows.html

If your system isn't so old that it's not supported, that article will walk you through writing a recursive query to do what you want.

If you know the highest value for sequence_number you can write a query that will work, but that's a bit ugly.  Go with Dave's function or the recursive SQL.


Kent
0
 
dking_wriAuthor Commented:
Thanks for all the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.