Concatenation - column values to row values 1

[spiderusa]

Hi

I need to create a report with basically three fields - Name, ID and Titles.

Name and ID are present in Customer Table
Titles is present in Details table

Basically there are several titles for each customer and each title is present as a row in the Details table. The Foreign key is ID. I need to concatenate all the titles of a customer into a single row in my report.
e.g.

John 101 President,Teacher,Coordinator
Tom 102 CEO, Recruiter, Board Member
etc.,

Any idea on how to do that either in Crystal Reports 9 or SQL?

Crystal Report - 9
Database - SQL Server 2000

I tried to read the FAQ and also other post son concatenation but didn't get an answer. Any help would be greatly appreciated.


Thanks.

 

[Turkbear]

Hi,
One way would be to insert a subreport that is linked by ID and is formatted to have multiple columns, Across then Down..
Group by ID and place the sub in the GH..

A better way, if possible, is to create a View or Stored Procedure that pre-creates the records with those items already included..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

If you insert a group on ID, you can then drag the name and ID into the group footer. To get the string of titles, there, create three formulas:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar titles := "";

//{@accum} to be placed in the details section:
whileprintingrecords;
stringvar titles := titles + {table.title} + ", ";

//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar titles;
left(titles, len(titles)-2);

Then suppress the group header and details section.

-LB

 

[spiderusa]

lbass

Thanks for the quick response. I tried your formulae and they worked perfectly. I then joined another table Order Details and each customer has more than one order. So if a customer has 5 orders, each title is repeated 5 times in the display.
I tried to insert another group but that didn't work. Any idea how to work around this. Thanks.

 

[lbass]

Change the formula to:

//{@accum} to be placed in the details section:
whileprintingrecords;
stringvar titles;

if instr(titles,{table.title}) = 0 then
titles := titles + {table.title} + ", ";

-LB

 

[spiderusa]

lbass,

Thanks for the inputs. One more question. What if there is a null value and I need to exclude it? I tried isnull(titles) and then isnull({table.title}). both didn't work.
Any suggestions?
Thanks.

 

[lbass]

Try:

//{@accum} to be placed in the details section:
whileprintingrecords;
stringvar titles;

if isnull({table.title}) then
titles := titles else
if instr(titles,{table.title}) = 0 then
titles := titles + {table.title} + ", ";

-LB