Order the result of a comma separated string generated with STUFF
As Aaron Bertrand pointed out in comments, the error is produced because I'm using SELECT DISTINCT; and in this example it is not necessary. Also, ORDER BY position is fully valid if the distinct is removed.

If DISTINCT is removed the query returns the desired value:




DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title)) 
                   FROM Foo
                   ORDER BY Position
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'');

SELECT @rows;
GO



| (No column name)                                                                  |
| :-------------------------------------------------------------------------------- |
|  M3 AS [Title1], M5 AS [Title2], M1 AS [Title3], M2 AS [Title10], M4 AS [Title12] |



dbfiddle here

I recommend having a look at this article, recommended by sp_BlitzErik:

Grouped Concatenation : Ordering and Removing Duplicates