Select distinct with order by in stuff function

Question

I would like to know if its possible to reorder the concatenated cells in stuff function based on another column.

As of now, I am using below query

SELECT 
    Id, 
    KPI = STUFF((SELECT DISTINCT ', ' + Name
                 FROM #TempTable1 b 
                 WHERE b.Id = a.Id 
                 FOR XML PATH('')), 1, 2, '')

but this makes an output in alphabetical order. I would like to order in based on an ID column.

Below is the result for my query

I would like to order it as QUALITY SCORE, ACCURACY. This is based on another column which is the ORDERID

What happens if you put the order by in the SELECT statement that is inside the STUFF? Do you get an error? — Robert Sievers, Jul 14 '21 at 20:20
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Jul 14 '21 at 20:28

score 1 · Answer 1 · answered Jul 14 '21 at 20:23

You will need to do three things:

Add an ORDER BY clause to your existing subquery that is being used to provide the rows to FOR XML PATH.
Remove DISTINCT for the aggregate and replace with GROUP BY
Determine how OrderID should influence the order of Name within your output.

Currently your subquery is using the DISTINCT operator, which is an aggregate. In order to access the value of OrderID within your ORDER BY clause then you need to aggregate it to the same level as your current output, which is Name. Since it looks like there can be multiple OrderID values per Name value, you have to determine how it makes sense to aggregate the OrderID value to influence the order of final output in this query. An example might be to use MIN to aggregate it.

Complete example below:

SELECT 
    Id, 
    KPI = STUFF((SELECT ', ' + Name
                 FROM #TempTable1 b 
                 WHERE b.Id = a.Id 
                 GROUP BY Name 
                 ORDER BY MIN(OrderId)
                 FOR XML PATH('')), 1, 2, '')

Thank you for the help and the detailed explanation on how it works ! This really helps me understand the problem correctly ! Thank you so much ! — Justin Matthew Tacogue, Jul 19 '21 at 23:30
I'm glad this helped. If this solved your question, please mark it as the answer. Thank you! — trenton-ftw, Jul 20 '21 at 06:18

Select distinct with order by in stuff function

1 Answers1