3

Suppose that i select into a temptable as follow by performing grouping from an existing table, and this is current temptable value:

Title Count
-------------
Sr.   11
Mrs.  2
Sra.  3
Ms.   415
Mr.   578

What I want to achieve is a comma separated value for title as follow, which is order by count

[Mrs.], [Sra.], [Sr.], [Ms.], [Mr.]

However, currently i can only able to achieve a comma separated value as follow, which is the default ordering in #temptable

[Sr.], [Mrs.], [Sra.], [Ms.], [Mr.]

Below is my code. How could i achieve what i wanted?
Note: I don't assume nor expect the sequence is already in order when inserting into the #temptable

DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(Title)
FROM
   (
    SELECT Title FROM #tempTable1
   ) AS P
6

You can use STUFF function in this way:

DECLARE @Columns nvarchar(max);

SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Title)
                      FROM tempTable1
                      ORDER BY [Count]
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'), 1, 1, '');

SELECT @Columns;
| (No column name)                     |
| :----------------------------------- |
|  [Mrs.], [Sra.], [Sr.], [Ms.], [Mr.] |

db<>fiddle here

Alternatively and depending on your SQL-Server version you can use STRING_AGG function:

DECLARE @Columns nvarchar(max);

SELECT
    STRING_AGG(QUOTENAME(Title), ',') WITHIN GROUP (ORDER BY [Count])
FROM
    tempTable1;

SELECT @Columns;
  • 2
    Adding that XML PATH or STRING_AGG with ORDER BY are the correct methods for aggregate string concatenation. TOP 100 PERCENT method will keep the parser happy but will not guarantee ordering or proper behavior, depending on the execution plan. – Dan Guzman Sep 2 '19 at 9:43
  • @DanGuzman edited. – McNets Sep 2 '19 at 13:00

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.