SQL SERVER Select Distinct ID is not working

Question

I'm trying to get through to this sql Select statement below but I can't get it right.

Select DISTINCT vc.cid, vt.tid, vc.device,
    STUFF((select ', ' + c.tName from thumbTbl t2
                    join tags v ON t2.cid = v.cid
                    join config c on v.tid = c.tid
                    where vc.cid = t2.cid
                    group by c.tName 
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(max)'), 1, 1, '') tName
from tags vt
    Inner join thumbTbl vc on vt.cid = vc.cid
    Left join config vtc on vt.tid = vtc.tid
order by vc.cid asc

The result as following:

cid tid device      tName
--- --- ---------   ---------
159 7   Mat Dwens    Escalation, Follow Up, More Benefits
159 11  Mat Dwens    Escalation, Follow Up, More Benefits
159 12  Mat Dwens    Escalation, Follow Up, More Benefits
160 7   Jeniffer P    Rectro
162 8   Marc Novice   More Benefits, Rectro
162 6   Marc Novice   More Benefits, Rectro
165 4   Jeniffer P    Follow up

Question: How to display only 1 row of each Selected Id?, I tried to use SELECT DISTINCT But still gave me the same result and above.

The result That I want to see have to be like below:

cid tid device      tName
------- ---------   ----------
159 12  Mat Dwens   Escalation, Follow Up, More Benefits
160 7   Jeniffer P  Rectro
162 8   Marc Novice More Benefits, Rectro
165 4   Jeniffer P  Follow up

Does anyone have an idea?

Answer 1

It's not DISTINCT you're after, it's a GROUP BY and an aggregate fucntion on tid. The results are DISTINCT as you have a different value for tid on each row, however, what you want is the MAX. Due to a lack of sample data this is untested, however, I think this is correct:

SELECT vc.cid,
       MAX(vt.tid) AS tid,
       vc.device,
       STUFF((SELECT ', ' + c.tName
              FROM thumbTbl t2
                   JOIN tags v ON t2.cid = v.cid
                   JOIN config c ON v.tid = c.tid
              WHERE vc.cid = t2.cid
              GROUP BY c.tName
             FOR XML PATH(''), TYPE).value('(./text())[1]', 'nvarchar(max)'),1,2,'') AS tName --Changed to use .text() and also removed leading space
FROM tags vt
     INNER JOIN thumbTbl vc ON vt.cid = vc.cid
     LEFT JOIN config vtc ON vt.tid = vtc.tid
GROUP BY vc.cid,
         vc.device
ORDER BY vc.cid ASC;

Answer 2

Remove the SELECT DISTINCT and the join to config in the outer query. Then fix the inner query so it only brings in what you want from config:

select vc.cid, vt.tid, vc.device,
       stuff((select ', ' + c.tName
              from config c on v.tid = c.tid
              where v.tid = c.tid
              for xml path(''), TYPE
             ).value('.', 'NVARCHAR(max)'
                    ), 1, 1, ''
            ) tNames
from tags vt join
     thumbTbl vc
     on vt.cid = vc.cid
group by vc.cid, vt.tid, vc.device
order by vc.cid asc