• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • 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 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
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
tigin44Commented:
try this

SELECT casenum, STUFF( 
	(SELECT ' ' + commentline 
	 FROM yourTable A 
	 WHERE A.casenum = B.casenum
	 ORDER BY A.seqnum
	 FOR XML PATH('')),1,1,'' ) AS comments
FROM yourTable B	
GROUP BY B.casenum 
ORDER BY B.casenum

Open in new window

0
 
Dave FordSoftware Developer / Database AdministratorCommented:

I'm working on a solution for you, but (in the mean-time) you'll probably get a better answer if you post this question to the DB2 topic area. There's some really great experts there.

http://www.experts-exchange.com/Database/DB2/

-- DaveSlash
0
 
dking_wriAuthor Commented:
Hello,

Thanks for your comments.  

Dave, I will post this in the DB2 section as well.

Tigin44, I think some of this syntax must not work in the older AS400 environment. Can you help me understand how STUFF and the XML are used in this query?

Thanks again for your time,
David
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
tigin44Commented:
I have proposed this as your tags include sql server 2005..

STUFF replaces the first occurence of ' '; which I used to seperate comments from each other; with an empty string.

FOR XML PATH is a structure to produce xml documents .... Her I used a specialized version of it.

I dont have enough info to rewrite this for an AS400 system... but I think you may adopt this if your system has xml support...
0
 
dking_wriAuthor Commented:
Tigin44,

I apologize. When I re-read my question is was pretty confusing. I've found that a lot of SQL Server syntax will work in this AS400 database and that's why I included the tag. But it looks like the STUFF() and XML are not compatible with this very old database. I've redirected my post to the DB2 environment. I really appreaciate your comments and your time.

David
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

What version of AS/400 are you using?
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
You've got this tagged for Programming for AS/400, and then you've got it tagged for SQL Server 2005.  What flavor of SQL are you looking for: AS/400 DB2 or MS SQL Server?

- Gary Patterson
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Sorry, had that sitting there open for a while and didn't see the earlier responses.

If you're at V5R4 on the AS/400 or later, try this:

http://bytes.com/topic/db2/answers/432951-combine-column-multiple-rows-into-one-row

If you are on an earlier release, then your options are more limited.  Best to write a program.

- Gary Patterson
0
 
dking_wriAuthor Commented:
Dave - I have a question into IT to find out what version AS400 we are on. I'm in accounting department at the moment and I have extremely limited access to systems.

Gary - thanks for the info. I'll look into the article you sent.


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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now