带Always Encrypted加密列的行转列（Pivot）查询优化及加密错误解决需求

首先我得把你的核心问题和场景理清楚，再给出针对性的解决方案——你需要将关联到同一个用户的多行加密数据转成单行多列，但现有方案要么性能极差，要么因为Always Encrypted的限制触发加密不匹配错误。

先明确你的场景与目标 #

示例表结构与数据

Person表

Data表（Topic/Rating列启用Always Encrypted）

期望输出

先解释为什么你之前的方案会出问题 #

1. 多次自连接方案性能差：你之前的WITH tbl + 多次INNER JOIN方案会重复扫描Data表N次（N是Index的数量），大数据集下IO开销爆炸，而且你的SQL里还有笔误（i1.Id应该是i1.PersonID，因为tbl没有Id列），这会进一步拖慢查询。
2. CASE+MAX方案触发加密错误：Always Encrypted的核心逻辑是服务器端无法处理加密数据，所有运算必须在客户端解密后执行。而MAX()是服务器端聚合函数，服务器无法对加密的Topic/Rating列执行聚合，所以触发Encryption scheme mismatch错误。

最优解决方案：用APPLY操作+索引优化 #

这个方案同时解决性能和加密兼容两个问题：

• 避免服务器端对加密列执行任何运算/聚合，只做数据匹配
• 用APPLY替代多次自连接，减少表扫描次数
• 配合索引实现极速查询

1. 静态方案（Index数量固定）

如果你的Index数量是固定的（比如只有1和2），直接用这个方案：

-- 先给Data表创建关键索引（必须加！性能提升的核心）
CREATE NONCLUSTERED INDEX IX_Data_PersonID_Index 
ON Data(PersonID, Index) 
INCLUDE(Topic, Rating); -- 包含加密列，避免回表

-- 核心查询
SELECT 
    p.Person,
    idx1.Topic AS [Index 1 Topic],
    idx1.Rating AS [Index 1 Rating],
    idx2.Topic AS [Index 2 Topic],
    idx2.Rating AS [Index 2 Rating]
FROM Person p
-- 一次性获取当前用户的Index=1数据
OUTER APPLY (
    SELECT Topic, Rating 
    FROM Data d 
    WHERE d.PersonID = p.ID AND d.Index = 1
) idx1
-- 一次性获取当前用户的Index=2数据
OUTER APPLY (
    SELECT Topic, Rating 
    FROM Data d 
    WHERE d.PersonID = p.ID AND d.Index = 2
) idx2
-- 如果要过滤掉没有对应Index数据的用户，把OUTER APPLY改成CROSS APPLY

2. 动态方案（Index数量不固定）

如果你的Index数量是动态变化的（比如可能有3、4...），用动态SQL生成列，同样兼容加密列：

DECLARE @cols NVARCHAR(MAX);
DECLARE @apply_clauses NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 生成所有Index对应的列名
SELECT @cols = STRING_AGG(
    CONCAT(
        'idx', d.Index, '.Topic AS [Index ', d.Index, ' Topic], ',
        'idx', d.Index, '.Rating AS [Index ', d.Index, ' Rating]'
    ), ', '
)
FROM (SELECT DISTINCT Index FROM Data) d;

-- 生成所有Index对应的APPLY子句
SELECT @apply_clauses = STRING_AGG(
    CONCAT('
        OUTER APPLY (
            SELECT Topic, Rating 
            FROM Data d 
            WHERE d.PersonID = p.ID AND d.Index = ', d.Index, '
        ) idx', d.Index), ' '
)
FROM (SELECT DISTINCT Index FROM Data) d;

-- 生成最终查询SQL
SET @sql = CONCAT(
    'SELECT p.Person, ', @cols, '
    FROM Person p ', @apply_clauses
);

-- 执行动态SQL
EXEC sp_executesql @sql;

关键注意事项 #

1. 必须加索引：上面的IX_Data_PersonID_Index索引是性能的核心，它让APPLY的子查询直接通过索引定位数据，避免全表扫描。
2. 避免服务器端操作加密列：永远不要在服务器端对Always Encrypted列执行聚合（MAX/MIN）、运算（加减乘除）、字符串操作，这些必须在客户端解密后处理。
3. APPLY vs 自连接：APPLY是针对每行Person执行一次子查询，而多次自连接是全表扫描N次，大数据集下APPLY的性能会比自连接高10~100倍。

内容来源于stack exchange