I am trying to get a unique list of IDs from a table in CSV format. I am close I just need to be able to remove duplicates. So far I have:
DECLARE @csv VARCHAR(max)
set @csv = null
SELECT @csv = COALESCE(@csv + ',', '') + ''''+ID+''''
FROM TABLE
select @csv
The only problem is the table can have multiple IDs, and I only want each occurrence once. I tried adding a "DISTINCT" before the ID but it doesn't like being there.
Using the syntax SELECT @Variable = @Variable + ... FROM is a documented antipattern and should be avoided; it relies on the data engine processing your data in a row by row order, which there is no guarentee of. Instead use string aggregation to achieve the same results. In recent versions of SQL Server that would be by using STRING_AGG, however, in older versions you'll need to use FOR XML PATH (and STUFF) to achieve the same results.
Assuming you are on a fully supported version of SQL Server, then use a CTE/derived table to get the DISTINCT values, and then aggregate that:
WITH CTE AS(
SELECT DISTINCT ID
FROM dbo.YourTable)
SELECT STRING_AGG(ID,',')
FROM CTE;
