How to get strings comma separated in ascending order with STRING_AGG()

Question

My table looks like this

Color  Order 
------------
Red    49
Blue   32
Green  80
Green  30
Blue   93
Blue   77
Red    20
Green  54
Red    59
Red    42
Red    81
Green  35
Blue   91

My Query is

SELECT Color, Count(*) AS Count, STRING_AGG(Order,',') AS AggOrder
FROM MyTable
GROUP BY Color

When I group by Color and aggregate I get un-sorted orders

Something like this

Color  Count  AggOrder
------------------------------
Red    5      49,20,59,42,81
Blue   4      32,93,77,91
Green  4      80,30,54,35

Problem : AggOrder is un-ordered 49,20,59,42,81

I want to order it

so the end result is

Color  Count  AggOrder
------------------------------
Red    5      20,42,49,59,81
Blue   4      32,77,91,93
Green  4      30,35,54,80

I tried this query

SELECT Color, Count(*) AS Count, STRING_AGG(Order,',') AS AggOrder
FROM MyTable
GROUP BY Color
ORDER BY Order

But this gives an error.

Any idea how to fix that?

score 5 · Answer 1 · edited Mar 30 '21 at 04:06

5

Add an ORDER BY clause to your call to STRING_AGG:

SELECT
    Color,
    COUNT(*) AS Count,
    STRING_AGG([Order], ',') WITHIN GROUP (ORDER BY CAST([Order] AS INT)) AS AggOrder
FROM MyTable
GROUP BY Color;

Two comments on the Order column. First, ORDER is a reserved SQL Server keyword, and the query you pasted in your question won't even run as given, because ORDER when used as a database object name needs to be escaped. You should avoid naming your columns, tables, etc. using keywords. Second, assuming the Order column be text, if you want to order using it as a number, you should cast first to integer.

edited Mar 30 '21 at 04:06

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

answered Mar 30 '21 at 03:03

Tim Biegeleisen

502,043
27
286
360

1

I think (or hope) it more likely that the `[Order]` column is an `int` and therefore this should read `STRING_AGG(CAST([Order] AS varchar(10))) WITHIN...` – Charlieface Mar 30 '21 at 03:33
@Charliefacepalm Yes...I also thought of this, you might be right. – Tim Biegeleisen Mar 30 '21 at 03:34

score 4 · Accepted Answer · edited Mar 30 '21 at 04:17

4

You can use within group syntax

SELECT Color
    , Count(*) AS Count
    , STRING_AGG([Order],',') WITHIN GROUP (ORDER BY [Order]) AS AggOrder
FROM MyTable
GROUP BY Color

edited Mar 30 '21 at 04:17

Dale K

25,246
15
42
71

answered Mar 30 '21 at 03:02

eshirvana

23,227
3
22
38

Kazi Mohammad Ali Nur Romel · Answer 3 · 2021-03-30T04:59:16.187

For SQL Server 2017 or higher version then you have already got your answer. But if SQL Server is older then 2017 you can use stuff() with XML PATH FOR() to get your desired result:

Since you are using SQL Server 2017 or higher please do not use this solution rather use the one with string_agg() since that one is much faster and easy to implement.

Schema:

 create table MyTable (color varchar(10), [order] int);
 insert into MyTable  values('Red',    49);
 insert into MyTable  values('Blue',   32);
 insert into MyTable  values('Green',  80);
 insert into MyTable  values('Green',  30);
 insert into MyTable  values('Blue',   93);
 insert into MyTable  values('Blue',   77);
 insert into MyTable  values('Red',    20);
 insert into MyTable  values('Green',  54);
 insert into MyTable  values('Red',    59);
 insert into MyTable  values('Red',    42);
 insert into MyTable  values('Red',    81);
 insert into MyTable  values('Green',  35);
 insert into MyTable  values('Blue',   91);

Query:

 SELECT color,count(*) [Count],
    STUFF((SELECT ',' + COALESCE(LTRIM(RTRIM([order])), '') 
    FROM MyTable mt 
        WHERE mt.color = m.color
        order by [order]
    FOR XML PATH('') ), 1, 1, ''
    ) as AggOrder
 FROM MyTable m
 group by color

Output:

color	Count	AggOrder
Blue	4	32,77,91,93
Green	4	30,35,54,80
Red	5	20,42,49,59,81

db<>fiddle here

How to get strings comma separated in ascending order with STRING_AGG()

3 Answers3