Concatenating rows into one field for summary

Hi,
I am looking for a way of putting multiple values from different rows into
one field, for example:
The dataset returns 3 rows, with two fields, first name and surname like
below:
FirstName Surname
Joe Bloggs
David Beckham
Sue Smith
I want to get all of those surnames, and put them say into the final totals
of the report so they look like Bloggs,Beckham,Smith.
Can anyone see a way of archiving this.
Cheers
LukeYou cant do it in the same query because you will be using the first name if
not then you can use this method. what you can do is to create a seperate
dataset with the following method and refer this dataset in your report in
final totals
select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
you
need to fillup. :-)
any problem let me know.
Amarnath
"lukethepunk" wrote:
> Hi,
> I am looking for a way of putting multiple values from different rows into
> one field, for example:
> The dataset returns 3 rows, with two fields, first name and surname like
> below:
> FirstName Surname
> Joe Bloggs
> David Beckham
> Sue Smith
> I want to get all of those surnames, and put them say into the final totals
> of the report so they look like Bloggs,Beckham,Smith.
> Can anyone see a way of archiving this.
> Cheers
> Luke
>|||Hi,
Thanks thats almost got me what i want (i'd never heard of the COALESCE
function!)
The only problem i've got now is there are duplicate values ending up in the
end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
Is there anyway i can keep them out using the sql, or will i have to use
some custom code in the report to keep them uniquie?
Cheers
Luke
"Amarnath" wrote:
> You cant do it in the same query because you will be using the first name if
> not then you can use this method. what you can do is to create a seperate
> dataset with the following method and refer this dataset in your report in
> final totals
> select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> you
> need to fillup. :-)
> any problem let me know.
> Amarnath
>
> "lukethepunk" wrote:
> > Hi,
> >
> > I am looking for a way of putting multiple values from different rows into
> > one field, for example:
> >
> > The dataset returns 3 rows, with two fields, first name and surname like
> > below:
> >
> > FirstName Surname
> > Joe Bloggs
> > David Beckham
> > Sue Smith
> >
> > I want to get all of those surnames, and put them say into the final totals
> > of the report so they look like Bloggs,Beckham,Smith.
> >
> > Can anyone see a way of archiving this.
> >
> > Cheers
> > Luke
> >|||Hi luke,
unfortunetly there is no such thing like distinct coalesce.. May be what you
can do is to take a distinct and then pass the result set to coalesce. Pl try.
Amarnath
"lukethepunk" wrote:
> Hi,
> Thanks thats almost got me what i want (i'd never heard of the COALESCE
> function!)
> The only problem i've got now is there are duplicate values ending up in the
> end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
> Is there anyway i can keep them out using the sql, or will i have to use
> some custom code in the report to keep them uniquie?
> Cheers
> Luke
>
> "Amarnath" wrote:
> > You cant do it in the same query because you will be using the first name if
> > not then you can use this method. what you can do is to create a seperate
> > dataset with the following method and refer this dataset in your report in
> > final totals
> > select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> > you
> > need to fillup. :-)
> >
> > any problem let me know.
> >
> > Amarnath
> >
> >
> >
> > "lukethepunk" wrote:
> >
> > > Hi,
> > >
> > > I am looking for a way of putting multiple values from different rows into
> > > one field, for example:
> > >
> > > The dataset returns 3 rows, with two fields, first name and surname like
> > > below:
> > >
> > > FirstName Surname
> > > Joe Bloggs
> > > David Beckham
> > > Sue Smith
> > >
> > > I want to get all of those surnames, and put them say into the final totals
> > > of the report so they look like Bloggs,Beckham,Smith.
> > >
> > > Can anyone see a way of archiving this.
> > >
> > > Cheers
> > > Luke
> > >|||Hi,
I have managed to get it working by passing the results to some custom code,
and looping through the string and sending back only one of each.
Thanks for your help
Luke
"Amarnath" wrote:
> Hi luke,
> unfortunetly there is no such thing like distinct coalesce.. May be what you
> can do is to take a distinct and then pass the result set to coalesce. Pl try.
> Amarnath
> "lukethepunk" wrote:
> > Hi,
> >
> > Thanks thats almost got me what i want (i'd never heard of the COALESCE
> > function!)
> >
> > The only problem i've got now is there are duplicate values ending up in the
> > end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
> >
> > Is there anyway i can keep them out using the sql, or will i have to use
> > some custom code in the report to keep them uniquie?
> >
> > Cheers
> > Luke
> >
> >
> > "Amarnath" wrote:
> >
> > > You cant do it in the same query because you will be using the first name if
> > > not then you can use this method. what you can do is to create a seperate
> > > dataset with the following method and refer this dataset in your report in
> > > final totals
> > > select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> > > you
> > > need to fillup. :-)
> > >
> > > any problem let me know.
> > >
> > > Amarnath
> > >
> > >
> > >
> > > "lukethepunk" wrote:
> > >
> > > > Hi,
> > > >
> > > > I am looking for a way of putting multiple values from different rows into
> > > > one field, for example:
> > > >
> > > > The dataset returns 3 rows, with two fields, first name and surname like
> > > > below:
> > > >
> > > > FirstName Surname
> > > > Joe Bloggs
> > > > David Beckham
> > > > Sue Smith
> > > >
> > > > I want to get all of those surnames, and put them say into the final totals
> > > > of the report so they look like Bloggs,Beckham,Smith.
> > > >
> > > > Can anyone see a way of archiving this.
> > > >
> > > > Cheers
> > > > Luke
> > > >