The first thing I would suggest is getting rid of your cteDates.  It is doing absolutely nothing except wasting CPU time.  If you decide that you need one in the...

Use a CTE/ROW_NUMBER().  Since you didn't provide consumable data, this isn't tested.

WITH CTE AS
(
     SELECT
        A.EMPLID
    ,    B.FIRST_NAME
    ,    B.LAST_NAME
   ...

This will work with most (all?) data types.  I used DENSE_RANK() to filter out duplicates, but ROW_NUMBER() would work in a similar fashion.

WITH CTE AS
(

There is a write-up on Dwain Camps' site.  Tally Tables.  If can can, you want to use that to create an inline table-valued function.

Drew

You query still doesn't contain a period, and I am not getting an error message when I run your code.  The problem is somewhere else.

Drew

You generally want to return either XML values or comma-separated values, not both.  It's not clear exactly what you are looking for, but the FOR XML clause can be used...

WITH Base AS ( SELECT * FROM (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0) ) t(n) )
, Tally(n) AS (SELECT ROW_NUMBER() OVER(ORDER BY...

I don't know that it's better, but you could split the string on spaces, filter the results, and then recombine the string.

Drew

There are two issues here.  The first one is identifying the correct group.  Your English description doesn't match the code.  Your English description says that the records should have the...