I have an extremely large query that I need to include a comma delimited list in. I'm accomplishing this with subqueries like so:
STUFF(( SELECT distinct ',' + t1.Name
FROM t2
inner join t1 ON t1.ID = t2.ID
WHERE t2.otherField = 12345
ORDER BY t2.ID
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') as talentName
In this particular case I need to add a distinct clause (as shown). However, when I do so I get the following error:
Msg 145, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
I understand why this error occurs, but I'm not sure what to do about it. t2.ID cannot be included in the select statement as it would then get returned as part of the resultset. I cannot remove the order by clause because the comma delimited list must be in an order that matches another list I'm generating of the IDs.
How can I insure that this comma delimited list is both distinct and in the proper order?
End Goal
To help clarify what I'm trying to accomplish, this query will pull comma delimited lists of both the t1.name and t1.ID. I'm currently doing this in two separate STUFF statements, but if there is an alternate method I'd be open to it. This query will return several thousand rows, so I'm attempting to find a set-based solution to avoid thousands of ad-hoc queries running each time our web page is loaded.
Aucun commentaire:
Enregistrer un commentaire