将Select结果按顺序插入变量

Question

数据

Approval_ID
-----------
1
2
3
4
5
6
7
8
9
10

查询

DECLARE 
    @id varchar(8000)

SELECT  @id = COALESCE(@id + ', ', '') + '[' +  Approval_ID  + ']'
FROM (
   SELECT DISTINCT Approval_ID
   FROM Gate_III_CS_Approval
) Y

结果总是

1,10,2,3,4,5,6,7,8,9

我试着增加订单

ORDER BY len(Approval_ID), Approval_ID

但有错误

ORDER子句在视图、内联函数、派生表、子查询和公共表表达式中无效，除非还指定了TOP、偏移量或FOR XML。

我想要这样的结果

1,2,3,4,5,6,7,8,9,10

我该怎么办？

最新情况：

   ;with cte as (
        SELECT DISTINCT Approval_ID
        FROM Gate_III_CS_Approval
    )

    SELECT @id = STUFF(
                    (SELECT concat(',', '[' + Approval_ID + ']') 
                        FROM cte ORDER BY len(Approval_ID), Approval_id
                        FOR XML PATH('')
                    ), 1, 1, '')

Answer 1

使用CTE和STUFF for XML PATH

-- replace CTE with your table, this is only an example

declare @id varchar(8000) 

;with cte as (
 select 1 n 
 union all
 select n+1 n from
   cte 
   where n < 10
)
select @id = 
   STUFF((
            SELECT concat(',', n)
            FROM cte
            order by n
            FOR XML PATH('')
            ), 1, 1, '')



select @id

programmatically cast n from alphanumeric to int for sorting

Answer 2

为此，您也许可以尝试将varchar转换为int。我建立了一个示例这里

DECLARE 
        @id varchar(8000)
    SELECT  @id = COALESCE(@id + ', ', '') + '[' +  CAST(ID AS VARCHAR(10))  + ']'
    FROM (
       SELECT DISTINCT CAST(id AS INT) ID
       FROM TABLA   
    ) Y

    SELECT  @ID

Answer 3

对于Approval_ID，您似乎使用的是varchar字段而不是整数。

对于基于varchar字段的排序，您可以尝试maSTAShuFu的答案中的方法。

在这里，我用问题中的查询更新它。

SELECT STUFF(
    (SELECT CONCAT(',[', Approval_ID,']')
     FROM <your_table>
     ORDER BY cast(Approval_ID as int)
        FOR XML PATH('')), 1, 1, '')