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 OrderIDs
FROM Sales.Orders o
INNER JOIN STRING_SPLIT(@input,',') x ON x.value = o.CustomerID
GROUP BY CustomerID
ORDER BY CustomerID;

Here is the result:

CustomerID  OrderIDs
----------- ------------------------------------------------------
1059        68716,69684,70534,71100,71720,71888,73260,73453
1060        72646,72738,72916,73081
1061        72637,72669,72671,72713,72770,72787,73340,73350,7335

You can see that the OrderIDs are sorted in ascending order. However, there is no guarantee that they will be sorted; the database engine will try to generate the result as fast as possible and does not care about the order of IDs. If you want to be sure that the comma-separated list is in a specific order, use the WITHIN GROUP clause. In this example, you use the same statement as in the preceding one, but this time you ensure that OrderIDs are stored in descending order:

DECLARE @input VARCHAR(20) = '1059,1060,1061';
SELECT CustomerID, STRING_AGG(OrderID, ',') WITHIN GROUP(ORDER BY OrderID DESC) AS orderids
FROM Sales.Orders o
INNER JOIN STRING_SPLIT(@input,',') x ON x.value = o.CustomerID
GROUP BY CustomerID
ORDER BY CustomerID;

You can see the expected result, but this time the order is guaranteed:

CustomerID  OrderIDs
----------- ------------------------------------------------------
1059        73453,73260,71888,71720,71100,70534,69684,68716
1060        73081,72916,72738,72646
1061        73356,73350,73340,72787,72770,72713,72671,72669,72637

Use the standard option WITHIN GROUP clause if you want to specify the order for the concatenation.

The functions in SQL Server 2017 are very useful and practical. You can achieve the same result with significantly less code. For instance, if you want to return a list of order IDs for customers given in a comma-separated list in the WideWorldImporters database prior to SQL Server 2016, you need to perform two steps. First, you need to create a user-defined function to handle values from a comma-separated list, and then you need to create a comma-separated list of order IDs for output by using XML:

CREATE FUNCTION dbo.SplitStrings 
( 
    @input NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(item NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT; 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @input);
    WHILE @start < LEN(@input) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@input) + 1;
       
        INSERT INTO @output (item)  
        VALUES(SUBSTRING(@input, @start, @end - @start)); 
        SET @start = @end + 1;
        SET @end = CHARINDEX(@delimiter, @input, @start);
    END 
    RETURN 
END
GO
SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
DECLARE @input VARCHAR(20) = '1059,1060,1061';
SELECT C.CustomerID,
 STUFF( (SELECT ',' + CAST(OrderId AS VARCHAR(10)) AS [text()]
 FROM Sales.Orders AS O
 WHERE O.CustomerID = C.CustomerID
 ORDER BY OrderID ASC
 FOR XML PATH('')), 1, 1, NULL) AS OrderIDs
FROM Sales.Customers AS C
 INNER JOIN dbo.SplitStrings(@input,',') AS F ON F.item = C.CustomerID
ORDER BY CustomerID;

In SQL Server 2017, you can use two built-in functions for that purpose and the code looks simple and clear, and even runs faster than the one from the previous SQL Server versions:

SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
DECLARE @input VARCHAR(20) = '1059,1060,1061';
SELECT CustomerID, STRING_AGG(OrderID, ',') WITHIN GROUP(ORDER BY OrderID ASC) AS OrderIDs
FROM Sales.Orders o
INNER JOIN STRING_SPLIT(@input,',') x ON x.value = o.CustomerID
GROUP BY CustomerID
ORDER BY CustomerID;

Note that both queries have set statistics IO and time flags in order to compare their execution parameters. Here is the output of the previous two executions:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 663, logical reads 1549, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#B12AC5B5'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 78 ms,  elapsed time = 87 ms.


SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 3, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

As you can see, new string functions are not only handy, but they perform better (sometimes significantly better) than the workarounds prior to SQL Server 2017.