Combine and display field

rowfei
rowfei used Ask the Experts™
on
Hi,

How can I combine same ID's comments and display it on each column?

Below is the current data output:

ID        Line     Type   Comment
23         1           A         This is
23         2           B          Test
24         3           B          Null
25         1           C          Testing
26         1           B          Testing


Below is what I want to display:

ID        Line     Type   Comment
23         1           A         This is Test
23         2           B         This is Test
23         3           B         This is Test
24         1           C          Testing
25         1           B          Testing


Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
I can't thin of an easy way to do what you want.  I assume your input data has some typos since it doesn't match the output.

Look at this report.  It uses variables and arrays to build the output.

mlmcc
CombineComments.rpt
Combine-comments.xls

Author

Commented:
Hi mimcc,

There's no typo since it's combined "Comment" of both ID#23, then display it on each ID#23 "Comment" field.

Any easy solution via SQL?

Thank you again for your help!!!
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I am not a SQL expert but I think it could be done in a stored procedure or view.

mlmcc
I think the typo that mlmcc was referring to is that you said that the current output has ID's 23, 23, 24, 25 and 26, but the desired output has ID's 23, 23, 23, 24 and 25.  I assume that 24, 25 and 26 in the first set should be 23, 24 and 25.  This may seem trivial, but we may not be of much help if we don't understand your data.

 So, you want to combine all of the Comment fields from records with the same ID?

 I assume that each line is a separate record.

 It would be one thing if you only wanted to show the combined comments on the _last_ line for each ID.  Then you could just combine the fields in a string variable and output that variable with the last record for each ID.  But I don't think there's an easy way to do that on the earlier lines, because CR hasn't read the later records yet.

 One brute force approach would be to replace the Comment field with a subreport.  You'd link the subreport on the ID (and any other required fields/parameters) and it would read the records for that ID, combine the Comment fields in a string variable, and output that variable at the end.

 The down side is that running a subreport for every record could _really_ slow down the report, but whether or not that's really a problem may depend on how much data you have, etc.  If the report is really short, it may not be a big deal.  If most ID's, or at least a significant number of them, have multiple records, you could improve the performance somewhat by only running the subreport once per ID, instead of for each record.  For example, group the main report by the ID, run the subreport in the group header and have it save the combined comments in a shared string variable, and then use that variable to display the comments in the detail section.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
DId you look at my solution?

NO subreport but it is a bit inflexible.

mlmcc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial