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