How to return comma delim

[Chris]

my select stmt will retun the following result set:
State
-------
CA
NV
UT
AZ

I want this to return as 1 field with the following comma separated list:
State
-------
CA, NV, UT, AZ

How to?

Thank you in advance,
Chris

[SQL Menace]

Take a look at
http://wiki.lessthandot.com/index.php/Concatenate_Values_From_Multiple_Rows_Into_One_Column
and
http://wiki.lessthandot.com/index.php/Concatenate_Values_From_Multiple_Rows_Into_One_Column_Ordered

the latter one is a SQL 2005 version and order is guaranteed

Denis The SQL Menace
http://www.lessthandot.com/
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

[Aaron Bertrand [SQL Server MVP]]

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-colu
mn-into-a-single-row.html

On 6/25/08 4:36 PM, in article
2A3549E4-D396-4359...@microsoft.com, "Chris"

[--CELKO--]

>> I want this to return as 1 field with the following comma separated list: <<

Do data formatting in the front end and not the database. Columns are
scalar values by definition, while fields can be pretty much any kind
of mess you want. Remember First Normal Form (1NF)?

[Plamen Ratchev]

If possible try to do this client side, it is easier and more appropriate to
implement there.

On SQL Server 2005 you can use FOR XML PATH with empty element name:

CREATE TABLE States (
[state] CHAR(2) NOT NULL PRIMARY KEY);

INSERT INTO States VALUES('CA');
INSERT INTO States VALUES('NV');
INSERT INTO States VALUES('UT');
INSERT INTO States VALUES('AZ');

SELECT STUFF(state_list, 1, 1, '') AS state_list
FROM (SELECT ', ' + [state]
FROM States
FOR XML PATH('')) AS S(state_list);

For SQL Server 2000 look for other solutions in the following article by
Anith Sen:
http://www.projectdmx.com/tsql/rowconcatenate.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[steve dassin]

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:aff3a679-8346-4b04...@y38g2000hsy.googlegroups.com...

Absence a list type a comma separated string is a mess much like in the
absence of the concept of fashion most people would appear to dress like a
shlump. The addition of a simple idea makes all the difference in the world.
Lets hope we don't have to put common sense on the endangered species list
:-)

For more on the gist of the list:
www.beyondsql.blogspot.com

[Uri Dimant]

my 2 cents

select stuff(

(select ',' + [state] as [text()] from States order by [state] for xml
path('')),

1, 1, '');

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:2FFDBB72-F862-408D...@microsoft.com...