Getting distinct values - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Getting distinct values

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-05 : 01:38:41

Hello guys,
I have a query as thus to get a CSV value, however when i add the keyword distinct to the query it only returns one value.

What is wrong here ?


declare @s varchar(max)
select distinct @s = isnull(@s + ',','') + country
from  history

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 01:41:48

how many distinct values you've in your table? whatdoes below return for you?

select count(distinct country) from history

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-05 : 01:57:28

Am using a dummy table for testing, Before deploying into the main environment.

I have 5 distinct values.

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 02:18:39

since you've posted this in 2005 forum, i guess you can try this also

DECLARE @s varchar(max)
SELECT  @s =LEFT(cl.CountryList,LEN(cl.CountryList)-1)
FROM (SELECT DISTINCT country + ',' AS [text()]
      FROM history
      FOR XML PATH(''))cl(CountryList)

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-05 : 02:37:13

works. Great

But what is the meaning of the xml code ?

FOR XML PATH(''))cl(CountryList)

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 02:44:55

quote:
Originally posted by mary_itohan

works. Great

But what is the meaning of the xml code ?

FOR XML PATH(''))cl(CountryList)

_____________________

Yes O !

it creates a dummy xml and returns is as text which will serve as comma seperated list.

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-05 : 03:11:05

thank u

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 03:35:08