-
June 5th, 2012, 10:17 PM
#1
[RESOLVED] Multiple Rows to a single row
I have a DB with a many-to-one relationship and I would like the 'Many' to be on one line.
In the example below Smith is a single Customer and is part of 4 regions.
Now:
Customer Region
Smith A
Smith B
Smith C
Smith D
What I want:
Customer Region
Smith A,B,C,D
Is this possible?
Thanks
-
June 5th, 2012, 10:20 PM
#2
Re: Multiple Rows to a single row
Clarification: 'Customer' and 'Region' are two different fields.
So I do not need them split up- I just need the Regions on one line seperated by Commas.
-
June 6th, 2012, 02:05 PM
#3
Re: Multiple Rows to a single row
Study the next solution, where we are concatenating a field and display the
resulting string at group or report footer in only one line
To do that, you have to create 3 formulas:
The first one, let's call it @null:
Code:
whileprintingrecords;
stringvar names := "";
Place it in your GH section, it will return you null.
The second one, let's say @comma_field:
Code:
whileprintingrecords;
stringvar names;
names := names & ", " & {table.field};
Place it in your Detail section which you can suppress; the formula will return you next:
, Jules, Christina, Paul
and the 3rd formula, let's call it @final (or whatever you like):
Code:
whileprintingrecords;
stringvar names;
Mid(names, 3);
Place it in your GF or report footer section.
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
June 9th, 2012, 05:03 PM
#4
Re: Multiple Rows to a single row
Thank you so much, this worked great!
-
December 3rd, 2015, 02:03 PM
#5
Re: Multiple Rows to a single row
Thanks. But, how can i clean the variable 'names' to show the records of the current group only?
-
December 4th, 2015, 01:01 AM
#6
Re: Multiple Rows to a single row
See Formula 1
You must place it in the Group Header Section, in order to clean the variable
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|