To get unique vaues you just need to group the entries for the columns (that applies to all kind of queries, not only to "FOR XML" ones :hehe: ):
SELECT a.ID,
a.ColA,
STUFF((SELECT ',' + b.ColB AS "text()"
FROM @t b
WHERE b.ID = a.ID
AND b.ColA = a.ColA
GROUP BY b.ColB -- << here
ORDER BY b.ColB
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColB,
STUFF((SELECT ',' + b.ColC AS "text()"
FROM @t b
WHERE b.ID = a.ID
AND b.ColA = a.ColA
GROUP BY b.ColC -- << and here
ORDER BY b.ColC
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColC
FROM @t a
GROUP BY a.ID,a.ColA
ORDER BY a.ID,a.ColA;
_____________
Code for TallyGenerator