SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL Distinct comma delimited list
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cable_si
Starting Member

20 Posts

Posted - 07/24/2013 :  07:09:12  Show Profile  Reply with Quote
Hi
I am trying to create a comma delimted list of names in a table using the below query

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product

SELECT @listStr

This works fine, however the list does contain duplicates

Can anyone advise how I would make this 'distinct' so the list does not contain duplicates

thanks

simon

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 07/24/2013 :  07:11:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE @listStr VARCHAR(MAX);

SELECT	@listStr = COALESCE(@listStr + ',', '') + Name 
FROM	(
		SELECT DISTINCT	Name
		FROM		Production.Product
	) AS d;

SELECT	@listStr



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

cable_si
Starting Member

20 Posts

Posted - 07/24/2013 :  07:15:57  Show Profile  Reply with Quote
thanks

quote:
Originally posted by SwePeso

DECLARE @listStr VARCHAR(MAX);

SELECT	@listStr = COALESCE(@listStr + ',', '') + Name 
FROM	(
		SELECT DISTINCT	Name
		FROM		Production.Product
	) AS d;

SELECT	@listStr



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/24/2013 :  07:20:23  Show Profile  Reply with Quote
Also

DECLARE @listStr VARCHAR(MAX);

SELECT	@listStr = STUFF(t.u,1,1,'')
FROM (SELECT DISTINCT	',' + Name
		FROM	Production.Product
      FOR XML PATH(''))t(u)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000