sqlsql-serversql-order-bysql-server-2017natural-sort

How to naturally ORDER BY with SQL Server's STRING_AGG()


I have a table #tmp like this:

recid       DefaultDimension    DefaultDimensionName    line_no
5637423499  Default             Employee                7
5637423499  1001                Product                 8
5637423499  100                 Region                  2
5637423499  1                   Routing                 17
5637423499  115                 Section                 5
5637423499  999                 Unit                    6
5637423499  1510                Branch                  3
5637423499  A                   CabinClass              14
5637423499  0B                  Carrier_Property        9
5637423499  103                 Channel                 16
5637423499  104                 Department              4
5637423499  113                 Division_CustomerType   1

I want to concat DefaultDimension and DefaultDimensionName columns based on ascending order of line_no

Here is the query I wrote but I'm not getting it in order:

select recid, 
STRING_AGG(DefaultDimension, '-')  WITHIN GROUP (ORDER BY line_no ASC) DefaultDimension,
STRING_AGG(DefaultDimensionName, '-') WITHIN GROUP (ORDER BY line_no ASC) DefaultDimensionName 
from #tmp
group by recid

Result:

recid       DefaultDimension                                    DefaultDimensionName
5637423499  113-A-103-1-100-1510-104-115-999-Default-1001-0B    Division_CustomerType-CabinClass-Channel-Routing-Region-Branch-Department-Section-Unit-Employee-Product-Carrier_Property

As you can see CabinClass should come at 3rd last position but it's coming at 2nd position.


Solution

  • Your line_no is varchar, as you can typically notice

    '1' < '14' < '16 < '17' < '2'

    So, just simply parse the varchar into int solve the problem.

    select recid, 
    STRING_AGG(DefaultDimension, '-')  WITHIN GROUP (ORDER BY CAST(line_no AS int) ASC) DefaultDimension,
    STRING_AGG(DefaultDimensionName, '-') WITHIN GROUP (ORDER BY CAST(line_no AS int)ASC) DefaultDimensionName 
    from #tmp
    group by recid