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.

Typed datasets are good ... in small quantities

Today I had to throw together an app that would allow a fine grain comparison (i.e. more control than VLOOKUP) of values between an Excel spreadsheet and a SQL Server. Pretty standard stuff I know, but this was my first pass at doing so using typed datasets for the SQL end (I like compile time checking), and pretty much datasets in general. I'm more of a Business Objects type of guy.

I have to say, it was actually really easy to use. The only thing holding me back from using them more often in my full scale app is the separation factor of the DAL from the BLL and not having a circluar reference. I typically use 3 classes: An object class (which is really just a dummy set of classes containing class properties), Data Access Class and a Business Logic class. I think it's possible to separate the typed dataset from the table adapters, but it wasn't something that's easily discerned/apparent. Plus with LINQ to SQL ...

Just for my reference ... to add a datarow from one datatable to another, use DataTable.ImportRow(datarow);