sqlsql-serverdistinctfor-xml-pathselect-for-xml

SQL SERVER Select Distinct ID is not working


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?


Solution

  • 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;