Coalesce combine rows into comma delimited string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Coalesce combine rows into comma delimited string

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-17 : 08:16:41

Simon writes "Hi

I've read article ID 2368 which illustrates the coalesce method of combining rows to a comma delimted string which is great but do you know any way for the results in the string to be distinct.

Say my query returned

1. A
2. B
3. A
4. A
5. B

How can I get the string to just contain "A,B" rather than "A,B,A,A,B"

Thanks"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 08:20:03

Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-17 : 08:33:12

Hi Simon, welcome to SQL Team!

Something like this perhaps?


DECLARE @strValues varchar(8000)
SELECT @strValues = COALESCE(@strValues+',', '') + MyColumn
FROM
(
    SELECT DISTINCT MyColumn
    FROM MyTable
) X
ORDER BY MyColumn

SELECT [Result] = @strValues

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 09:17:03

Kris, I like the way you welcome the new members

The @strValues will have comma as the first character
So you can remove it by

SELECT [Result] = substring(@strValues,2,len(@strValues))

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-12-16 : 16:02:56

You forgot to use a cursor

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources