Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-05-05
10
Medium Priority
?
405 Views
Last Modified: 2012-08-13
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
Comment
Question by:dking_wri
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 668 total points
ID: 35701736
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
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 668 total points
ID: 35701831

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
 

Author Comment

by:dking_wri
ID: 35702135
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 26

Expert Comment

by:tigin44
ID: 35702193
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
 

Author Comment

by:dking_wri
ID: 35702240
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
 
LVL 18

Expert Comment

by:Dave Ford
ID: 35702259

What version of AS/400 are you using?
0
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 35702589
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
 
LVL 36

Accepted Solution

by:
Gary Patterson earned 664 total points
ID: 35702605
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
 

Author Comment

by:dking_wri
ID: 35702674
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
 

Author Closing Comment

by:dking_wri
ID: 35705996
Thanks for all the help!
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question