Order the result of a comma separated string generated with STUFF Given a schema like this:

CREATE TABLE Foo
(
    Id int PRIMARY KEY,
    Position int NOT NULL,
    Title varchar(10) NOT NULL
);

INSERT INTO Foo VALUES
(1, 3,  'Title3'),
(2, 10, 'Title10'),
(3, 1,  'Title1'),
(4, 12, 'Title12'),
(5, 2,  'Title2');


I need to generate a comma separated string ordered by Position:

'M' + Id + ' AS [' + Title + ']'


Desired result: 

M1 AS [Title1], M2 AS [Title10], M3 AS [Title3], M4 AS [Title10], M5 AS [Title12]


I've tried:

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

SELECT @rows;


But it builds the result ordered by Id:

M1 AS [Title3], M2 AS [Title10], M3 AS [Title1], M4 AS [Title12], M5 AS [Title2]


If I add ORDER BY Id to the STUFF expression:

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('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;


Produces the next error:


  Msg 145 Level 15 State 1 Line 2
  ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
  +


I can use a subquery ordered by Position:

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

SELECT @rows;

M1 AS [Title3], M2 AS [Title10], M3 AS [Title1], M4 AS [Title12], M5 AS [Title2]


But I wonder if there is another way to order the result without using a subquery. There are no duplicated titles.

dbfiddle here
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