Some T-SQL help

This is mostly for my reference, but it might help someone else...

I needed to compile some information into an Excel spreadsheet from the main SQL DB. I needed one of the columns returned to be a concatenated value of a resultset.



For example, I wanted the end result to look like:



Column 1 Column 2

--------- ---------

Client A Account1, Account2, Account3



The normal result would have looked like:



Column 1 Column 2

--------- ---------

Client A Account1

Client A Account2

Client A Account3



After chasing around a few options, I ended up using a SQL function that took in the Client and using COALESCE + a variable, pulled the accounts into one varchar.

The function ended up looking like the following:

alter function fn_accountsforclient(@id int) returns varchar(8000) as 
begin
    declare @returnVal varchar(8000)
    select @returnVal = coalesce(@returnVal + ', ' + a.account, a.account)
    from ACCOUNTS a
    where a.clientID=@id
    return @returnVal

I'm just wondering if there was another way using straight SQL Select statements (i.e could have been executed from Excel's DB Query) instead of using a function? Since T-SQL is not my main programming focus, I know I am seriously lacking at more efficient ways of doing things.