It's been quite awhile since I posted a how2 entry, which are consistently my biggest source of traffic from searches...so here goes another...

A few weeks ago I was looking to create a table in an ASP.NET page with one of the columns containing the result set of multiple rows from one of the SQL tables.  Basically, I wanted to concatenate multiple rows returned from a T-SQL query into one long string and dump that into the ASP.NET dataset.  Make sense?  Probably not so here's an example:

SqlTable1 contains two columns, Team and Person.  A person can be a member of multiple teams so a select * from SqlTable1 might look like this:

Team - Person
A - Mel
A - Danny
A - Rene
B - Mel
B - Danny
B - Joe
C - Rene
C - Chris

And I want to return this in my ASP.NET dataset:

Team - Members
A - Mel, Danny, Rene
B - Mel, Danny, Joe
C - Rene, Chris

Hopefully that's clear now.  :)  You would think that a powerful database application like SQL 2000 would be readily capable of something like this and it is, but it wasn't obvious to me (or most of the internet) how to do it.

I did several searches via MSN Search and Google and finally ended up implementing this solution using a temporary table and a staging table in SQL to hold my results.  Very ugly and hackish, but it works.

Then a few days later Emery (my cohort in crime at work) and I were looking over my code when he shared the 'easy' way with me (he had just learned it himself a few weeks before).

DECLARE @list VARCHAR(8000)
SELECT @list = ISNULL(@list,") + ',' + Person
FROM SqlTable1
WHERE Team = 'A'

If you perform a SELECT @list it will return "Mel, Danny, Rene."  You can even wrap this up in a SQL function if you like.

Hope that helps!

~tod