FOR XML and the need for DISTINCT

[Dan Holmes]

The following code will only include the 'OFFERED' value once but it will leave a trailing comma. If the DISTINCT is
added the trailing comma is gone. Why? If the results are returned instead of placing them in the variable then both
'OFFERED' are there.

Change tblCalendar to your own numbers table.

DECLARE @coordination_state_cds VARCHAR(20), @Filters INT;
SET @Filters = 16424
SET @coordination_state_cds = REPLACE(
(
SELECT CASE
--SELECT DISTINCT CASE
WHEN @Filters & POWER(2,id) = 8 THEN 'OFFERED'
WHEN @Filters & POWER(2,id) = 32 THEN 'COORDINATED'
WHEN @Filters & POWER(2,id) = 16384 THEN 'OFFERED'
END as 'data()'
FROM tblcalendar
--3 is 8 and 14 16384, only need that range.
WHERE ID BETWEEN 3 AND 14
FOR XML PATH('')
), ' ', ',');
PRINT @coordination_state_cds

thanks
dan

[Mark]

Change

DECLARE @coordination_state_cds VARCHAR(20)

to

DECLARE @coordination_state_cds VARCHAR(100)