FOR XML PATH Ignore Alphabetical Order

Question

I am using the solution found here but I need to ignore the ordering in FOR XML PATH.

The sample data is:

ID      DisplayName
1        Editor
1        Reviewer
7        EIC
7        Editor
7        Reviewer
7        Editor
19       EIC
19       Editor
19       Reviewer

And the solution's result is:

ID      DisplayName
1        Editor, Reviewer
7        Editor, EIC, Reviewer
19       Editor, EIC, Reviewer

I need to find a way to stop FOR XML PATH from arranging the strings and return results in the exact order like:

ID      DisplayName
1        Editor, Reviewer
7        EIC, Editor, Reviewer, Editor
19       EIC, Editor, Reviewer

UPDATE:

My table looks something like:

ID      DisplayName      Sequence
1        Editor           1
1        Reviewer         2
7        EIC              1
7        Editor           2
7        Reviewer         3
7        Editor           4
19       EIC              1
19       Editor           2
19       Reviewer         3

So I want it to combine strings based on the sequence of each ID.

There is no "exact" order unless you specify a column which generates the ordering in your sample one column output. Actually, even the order you currently see could change at any time, and SQL Server is free to use any order it wishes. — Tim Biegeleisen, Oct 19 '17 at 02:56
@TimBiegeleisen Thank you for your response Tim, please see the update on the question I made. — Kevin, Oct 19 '17 at 03:04

score 2 · Accepted Answer · edited Jan 06 '22 at 03:03

2

SQL tables represent unordered sets. There is no ordering in the rows, unless a column specifies the ordering. Hence, you cannot have the results be "in the same order" as the original rows because the concept is meaningless.

If you do have an ordering column, you can use:

SELECT id,
        STUFF( (SELECT ', ' + DisplayName
                FROM #t b 
                WHERE b.ID = a.ID
                ORDER BY b.Sequence
                FOR XML PATH('')
               ), 1, 2, ''
             ) as displaynames
FROM (SELECT DISTINCT ID FROM #t) a;

Here Sequence is the column that specifies the ordering per your updated question.

edited Jan 06 '22 at 03:03

Nimantha

6,405
6
28
69

answered Oct 19 '17 at 02:58

Gordon Linoff

1,242,037
58
646
786

I tried this one but I did not work. Please see this SQLFiddle http://www.sqlfiddle.com/#!6/b2e655/5. Thank you. – Kevin Oct 19 '17 at 03:09
I got it working now, just some error on the table name. Thank you. – Kevin Oct 19 '17 at 03:14

FOR XML PATH Ignore Alphabetical Order

1 Answers1