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
| Name | Order |
|---|---|
| A | 1 |
| B | 2 |
| C | 3 |
TrackingCode Table
| ID | Code |
|---|---|
| 123 | A |
| 321 | B |
| 159 | C |
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