In my database I have a Tracking Codes table as a child of a single Employment table. Along with other data, I am trying to return all of the Tracking Codes in a comma separated string so there will only be one row per Employment. However, I want this string sorted by the Tracking Code Order table that will give a numerical order depending on the Tracking Code.

My Tracking Code Tables look like this:

TrackingCodeOrder Table

TrackingCode Table

The current code I am using is:

(
    select top(1) string_agg(tc.TrackingCode, ',') 
    from TrackingCode as tc 
    left join TrackingCodeOrder tco on tco.Name = tc.TrackingCode
    where etc.Employment_id = e.id
    group by tco.Order
    order by tco.Order asc
) as 'TrackingCodeId'

from employee e

When I attempt to do it with this code, it runs, but I do not get all of my tracking codes returned. Some have 2 but will only return 1, others will have 9 but only return 4. If I remove the top(1), group by, and order by, I will see all the codes returned correctly, but not in the expected order. I have also attempted using top 100 percent. But the sub select will return more than one item.

Is there another way that I can sort in this sub select?

>Solution :

Provided your database compatibility is set to an appropriate level, then you need to use the specific order clause for STRING_AGG

STRING_AGG(tc.TrackingCode, ',') WITHIN GROUP (ORDER BY tco.Order ASC)

Read more at the official MS docs for SQL, STRING_AGG() function:
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16