Combine and display field


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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike McCrackenSenior ConsultantCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rowfeiAuthor 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 ConsultantCommented:
I am not a SQL expert but I think it could be done in a stored procedure or view.

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.

Mike McCrackenSenior ConsultantCommented:
DId you look at my solution?

NO subreport but it is a bit inflexible.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.