5

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

  • i think you are asking part of your main problem,so not able to think why you will need such output. – KumarHarsh May 4 '17 at 8:18
  • To be clear the only role STUFF plays here is removing the surplus delimiter. The bulk of the generation is done by XML PATH. – Martin Smith May 4 '17 at 16:06
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.