Hi,
I am looking for a type of aggregate function for a string. Instead of finding a Max value or the average of a column, I would like to build one string value holding the aggregate.
Example:
Source data
RecordID PersonID Name Course Score
1 1 Fred Maths 70
2 1 Fred Science 78
3 2 Mary Maths 65
4 2 Mary Science 60
5 2 Mary History 85
I would like my query to return the following resultset:
Name Scores
Fred 70; 78
Mary 65; 60; 85
Hi R2 DJ,
In your ASP.NET Application, using DataGrid web server control and implementing ItemDataBound event.
Good Coding!
Javier Luna
http://guydotnetxmlwebservices.blogspot.com/
|||
You could use UDF's, but there are some limitations/dificulties.
For instance, the next sample is simple, but will only work for response sizes until varchar (max). You could do the same with text, but it would be a little bit more complicated:
create FUNCTION dbo.ConcatenateEmployeeCustomers
(
-- Add the parameters for the function here
@.iIDEmployee int
)
RETURNS varchar ( max )
AS
BEGIN
declare @.vcTotal as varchar (max )
set @.vcTotal = ''
select @.vcTotal = @.vcTotal + ISNULL ( CustomerID + ',' , '')
From orders
where EmployeeID = @.iIDEmployee
RETURN @.vcTotal
END
GO
select dbo.ConcatenateEmployeeCustomers ( EmployeeID ) , *
from employees
|||In SQL Server 2005, you can do this using XML. For
your case, it would look something like this:
-- Adapted from an example posted by Erland Sommarskog
select
Name,
substring(IdList, 1, datalength(IdList)/2 - 1)
-- strip the last ',' from the list
from (
select distinct Name from YourTable
) as c -- or use a Names table if one exists
cross apply (
select
convert(nvarchar(30), PersonID) + ',' as [text()]
from YourTable as o
where o.PersonID = c.PersonID
order by o.RecordID
for xml path('')
) as Dummy(IdList)
Steve Kass
Drew University
www.stevekass.com
R2 DJ@.discussions.microsoft.com wrote:
> Hi,
>
> I am looking for a type of aggregate function for a string. Instead of
> finding a Max value or the average of a column, I would like to build
> one string value holding the aggregate.
>
> Example:
>
> Source data
>
> RecordID PersonID Name Course
> Score
>
> 1 1 Fred
> Maths 70
>
> 2 1 Fred
> Science 78
>
> 3 2 Mary
> Maths 65
>
> 4 2 Mary
> Science 60
>
> 5 2 Mary
> History 85
>
> I would like my query to return the following resultset:
>
> Name Scores
>
> Fred 70; 78
>
> Mary 65; 60; 85
>
>
