SQL Server – How to Order Results of a Comma Separated String Generated with STUFF

csvorder-bysql server

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

Best Answer

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

Related Question