SQL Distinct comma delimited list

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

Transact-SQL (2012)

SQL Distinct comma delimited list

Author

Topic

cable_si
Starting Member

20 Posts

Posted - 2013-07-24 : 07:09:12

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

30421 Posts

Posted - 2013-07-24 : 07:11:47

[code]DECLARE @listStr VARCHAR(MAX);

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

SELECT @listStr[/code]

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

cable_si
Starting Member

20 Posts

Posted - 2013-07-24 : 07:15:57

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-24 : 07:20:23

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources