SQL statement: Is this Possible?

[Aaron]

I need a SQL statement to take data that would normally look like this
in a result set (SELECT ID, Name FROM Employee):

ID Name
-- -------
1 Aaron
2 Mike
3 Eric

To look like this, in one row and column with commas separating each
Name:

Names
-------------------
Aaron, Mike, Eric


Any examples of how this might be accomplished? Thanks.

[Erland Sommarskog]

Re: SQL statement: Is this Possible?

Aaron (arockwel@yahoo .com) writes:[color=blue]
> I need a SQL statement to take data that would normally look like this
> in a result set (SELECT ID, Name FROM Employee):
>
> ID Name
> -- -------
> 1 Aaron
> 2 Mike
> 3 Eric
>
> To look like this, in one row and column with commas separating each
> Name:
>
> Names
> -------------------
> Aaron, Mike, Eric
>
> Any examples of how this might be accomplished? Thanks.[/color]

This is one of the few cases where you need to run a cursor (or some
other iterative scheme) to achieve the result. You can do:

SELECT @var = CASE @var IS NULL
THEN ''
ELSE @var + ', '
END + Name
FROM tbl

and you may get the desired result, but the actual result of such a query
is undefined, so you may get something else as well.



--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[WangKhar]

Re: SQL statement: Is this Possible?

unless you actually want a csv - when you could bcp it out to a file.