need help with a possible coalesce query

[DanC]

I'm in need of some help here. Below is an example of what I am trying to do:

table ex.

company_name registered date amount

company1 VA 1/1/2003 5
company2 GA 4/3/2002 10
company1 MD 7/25/2002 4
company1 NC 8/2/2001 9
company3 CA 9/19/2000 25
company3 TX 12/25/2002 100


I need results to look like:

company1 VA, MD, NC 18
company2 GA 10
company3 CA, TX 125

I've been playing around with the coalesce function but can't get it to return just what I need. I will also need to be able to specify date ranges in the where clause too.
Any help would be greatly appreciated.

 

[swampBoogie]

select company_name,sum(amount), dbo.createList(company_name)
from t
group by company_name

where createList is defined as

create function createList(@company_name varchar(20))
returns varchar(30)
as
declare @result varchar(30)
select @result = coalesce(@result + ',','') + registered
from t
where company_name = @company_name
return @result

 

[DanC]

Nice, thanks a lot. I think the function was missing the beginning and end statements, and I need to add a group by in the function to account for duplicate states. If this is wrong, let me know. Thanks again for the help.

create function createList(@company_name varchar(20))
returns varchar(30)
as
begin
declare @result varchar(30)
select @result = coalesce(@result + ',','') + registered
from t
where company_name = @company_name
group by registered
return @result
end

 

[swampBoogie]

Yes, I missed the begin end. Otherwise it looks okay as far as I can see.