March 2018
Intermediate to advanced
816 pages
19h 35m
English
By using the WITHIN GROUP clause, you can define the order for concatenated values. The following code returns order IDs for three given customers from the Sales.Orders table:
DECLARE @input VARCHAR(20) = '1059,1060,1061';SELECT CustomerID, STRING_AGG(OrderID, ',') AS OrderIDsFROM Sales.Orders oINNER JOIN STRING_SPLIT(@input,',') x ON x.value = o.CustomerIDGROUP BY CustomerIDORDER BY CustomerID;
Here is the result:
CustomerID OrderIDs----------- ------------------------------------------------------1059 68716,69684,70534,71100,71720,71888,73260,734531060 72646,72738,72916,730811061 72637,72669,72671,72713,72770,72787,73340,73350,7335
You can see that the OrderIDs are sorted in ascending order. However, there is no ...