Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

need help with a possible coalesce query

Status
Not open for further replies.

DanC

Programmer
Joined
Jan 12, 2001
Messages
65
Location
US
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.
 
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
 
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
 
Yes, I missed the begin end. Otherwise it looks okay as far as I can see.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top