如何将CTE提取的排序列存入变量？动态PIVOT列排序问题

问题根源 #

1. TOP 100 PERCENT + ORDER BY 不生效的原因
   SQL Server的查询优化器会判定TOP 100 PERCENT返回所有行，此时ORDER BY只是语法上允许，但不会实际执行排序——因为优化器认为返回全量数据时排序是多余的操作，所以CTE的输出顺序无法得到保证，后续拼接自然会按非预期的顺序（比如名称排序）生成结果。

2. 后续分组排序无效的原因
   传统的字符串拼接操作（比如FOR XML PATH）如果没有基于已明确排序的结果集执行，会默认按照底层数据的存储顺序或优化器选择的执行计划顺序拼接，分组操作本身并不会强制保证排序后的顺序。

解决方法 #

方法1：使用STRING_AGG（SQL Server 2017及以上版本推荐） #

STRING_AGG函数支持通过WITHIN GROUP子句直接指定聚合拼接时的排序规则，这是最简洁可靠的方式：

DECLARE @Components NVARCHAR(MAX)

SELECT @Components = STRING_AGG(CONCAT('[', strName, ']'), ',') 
                     WITHIN GROUP (ORDER BY dblWeighting DESC)
FROM (
    -- 替换为你的CTE关联查询逻辑，无需TOP 100 PERCENT
    SELECT strName, dblWeighting
    FROM 表1
    JOIN 表2 ON 表1.关联字段 = 表2.关联字段
    -- 其他过滤条件
) AS ComponentData

方法2：表变量/临时表预存排序结果（兼容旧版本） #

先将排序后的结果存入表变量或临时表，确保行的顺序，再执行拼接：

DECLARE @Components NVARCHAR(MAX)
DECLARE @SortedComponents TABLE (strName NVARCHAR(255), dblWeighting DECIMAL(18,2))

-- 插入已排序的数据到表变量
INSERT INTO @SortedComponents (strName, dblWeighting)
SELECT strName, dblWeighting
FROM 表1
JOIN 表2 ON 表1.关联字段 = 表2.关联字段
-- 其他过滤条件
ORDER BY dblWeighting DESC

-- 执行字符串拼接
SELECT @Components = COALESCE(@Components + ',', '') + CONCAT('[', strName, ']')
FROM @SortedComponents

核心原则：不要依赖CTE或普通SELECT的ORDER BY来保证后续操作的顺序，必须通过明确的排序约束（如STRING_AGG的WITHIN GROUP、INSERT...ORDER BY）来固定行的顺序。

内容的提问来源于stack exchange，提问作者Andrew Richards