Concatenating Unique Field Values

Lynn's solution does a great job of concatenating field values into one string. However, it repeats duplicates, which can be quite annoying with many duplicates.

If we change the code to create the table and populate to:

declare @TestTab table (ColA int, ColB Varchar(10));

insert into @TestTab

select 1, 'John' union all

select 1, 'Bob' union all

select 1, 'Josh' union all

select 1, 'Bob' union all

select 1, 'Bob' union all

select 1, 'Bob' union all

select 2, 'Kathy' union all

select 2, 'Jill'

;

Then we get the results:

1John, Bob, Josh, Bob, Bob, Bob

2Kathy, Jill

If we change her solution to add the "distinct" keyword, we only get unique values.

select * from @TestTab;

select

ColA,

stuff((select distinct ', ' + ColB from @TestTab t2 where t2.ColA = t1.ColA for xml path('')),1,2,'')

from

@TestTab t1

group by

ColA;

Then we get the unique results:

1Bob, John, Josh

2Jill, Kathy

Thanks Lynn for a marvelous piece of code that I will use many times in the future.

Bruce