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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -