• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 743
  • Last Modified:

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
0
dking_wri
Asked:
dking_wri
2 Solutions
 
momi_sabagCommented:
for older version you can not use recursive sql
you will have to write a function
0
 
Dave FordSoftware 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
 
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 OlsenData 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now