10

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.

Nalaka526
  • 11,278
  • 21
  • 82
  • 116
level_zebra
  • 1,503
  • 6
  • 25
  • 44
  • http://blog.sqlauthority.com/2009/01/15/sql-server-remove-duplicate-entry-from-comma-delimited-string-udf/ That article should prove extremely useful for you. – Alec. Jul 24 '13 at 11:10

1 Answers1

18

Is it useful ?

DECLARE @listStr VARCHAR(MAX) 
SELECT @listStr = COALESCE(@listStr+',' ,'') + name 
FROM (SELECT DISTINCT name FROM Production.Product) t
SELECT @listStr
Nalaka526
  • 11,278
  • 21
  • 82
  • 116
Vinit Prajapati
  • 1,593
  • 1
  • 17
  • 29