SQL Server: How to concatenate field text across rows

Today I needed to concatenate text in a fields across rows. Specifically, given a table with columns for user accounts and quotas, report summing the user accounts, a list of the quota labels, and the summed quota amounts.

What you would want is something like this (But this won't work!):

-- WARNING: THIS WON'T WORK!
select user_accountID, concat(quotaNo), sum(quotaAmount)
  from user_accounts
 group by user_accountID

Alas, in SQL Server 2000, there isn't a built-in function to do this. About the only way I know of to do this is to custom build a user-defined function for the particular table you are working with. Here is my example:

-- Create a test table
create table user_accounts (user_accountID varchar(32), quotaNo varchar(32), quotaAmount float)

insert into user_accounts values('user_account1', 'quota1', 100)
insert into user_accounts values('user_account1', 'quota2', 200)
insert into user_accounts values('user_account1', 'quota3', 400)

insert into user_accounts values('user_account2', 'quota4', 800)
insert into user_accounts values('user_account2', 'quota5', 1600)

go

-- Create the user defined function
create function concat_quota_number(@user_account varchar(20))
returns varchar(200) as
begin
  declare @str varchar(200)
  select @str = coalesce(@str + ', ', '') + quotaNo from user_accounts where user_accountID = @user_account group by quotaNo
  return @str
end

go

-- How to use it
select user_accountID, dbo.concat_quota_number(user_accountID) quota_labels, sum(quotaAmount) quota_sum
from user_accounts
group by user_accountID, dbo.concat_quota_number(user_accountID)

user_accountID	quota_labels	quota_sum
user_account1	quota1, quota2, quota3	700.0
user_account2	quota4, quota5	2400.0