SQL Server动态Pivot查询超varchar最大长度，求可行解决方案

针对你遇到的动态行转列时的字符串长度错误，以及寻找无需拼接SQL的动态Pivot方案，我来给你详细解答：

一、解决"Dynamic Pivot query exceeds maximum length of varchar"错误 #

你当前的问题核心是字符串拼接时的长度限制，虽然你用了VARCHAR(MAX)，但可以通过两个优化点彻底解决：

1. 改用NVARCHAR(MAX)存储查询语句 #

SQL Server中VARCHAR(MAX)理论上支持2GB存储，但如果拼接过程中涉及Unicode字符，或者某些中间步骤触发了非MAX字符串的截断，就可能报错。建议把@query及相关字符串变量改为NVARCHAR(MAX)，同时所有字符串常量前加N前缀，确保拼接过程不丢失长度：

declare @permission_id as NVARCHAR(7);
declare @counter as int;
declare @query as NVARCHAR(MAX); -- 改为NVARCHAR(MAX)
declare @name as NVARCHAR(15);
set @permission_id = N'permission_'; -- 加N前缀
set @query = N'SELECT a.name, '; -- 加N前缀
set @counter = 0;
WHILE @counter < 3 BEGIN
PRINT @counter;
SET @counter = @counter + 1;
SET @name = (SELECT CONCAT(@permission_id, @counter));
SET @query = @query + N' MAX(case when seqnum = '; -- 加N前缀
SET @query = (SELECT CONCAT(@query, @counter))
SET @query = @query + N' then a.permission end) as '; -- 加N前缀
SET @query = (SELECT CONCAT(@query, @name)) + N', '; -- 添加列分隔逗号
END
-- 移除最后一个多余的逗号
SET @query = LEFT(@query, LEN(@query) - 2);
-- 拼接查询剩余部分
SET @query = @query + N' from (select a.*, row_number() over (partition by a.name order by a.name) as seqnum from apps a ) a group by a.name;';
EXEC sp_executesql @query;

2. 用STRING_AGG替代WHILE循环生成列（更高效可靠） #

手动WHILE循环拼接容易出错，还可能因为多次拼接导致性能问题。SQL Server 2017及以上版本支持STRING_AGG函数，可以一键生成所有列的CASE语句，代码更简洁且避免截断：

DECLARE @cols NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

-- 自动生成所有权限对应的列表达式
SELECT @cols = STRING_AGG(
    CONCAT(N'MAX(CASE WHEN seqnum = ', seqnum, N' THEN permission END) AS permission_', seqnum),
    N', '
)
FROM (
    -- 获取每个应用下的权限序号（自动适配所有可能的权限数量）
    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY name ORDER BY permission) AS seqnum
    FROM apps
) AS seqnums;

-- 拼接完整的动态查询
SET @query = CONCAT(N'
SELECT name, ', @cols, N'
FROM (
    SELECT name, permission, 
           ROW_NUMBER() OVER(PARTITION BY name ORDER BY permission) AS seqnum
    FROM apps
) AS src
GROUP BY name;
');

-- 执行动态查询
EXEC sp_executesql @query;

这种方式不仅代码更简洁，还能自动适配任意数量的权限（只要不超过SQL Server默认的1024列限制），同时完全避免了WHILE循环的拼接错误。

二、无需拼接SQL的动态Pivot方案？ #

很遗憾，SQL Server目前没有原生支持无需拼接SQL的动态Pivot功能。因为SQL是静态编译语言，Pivot子句中的列必须在查询编译时确定，无法在运行时动态扩展列。

如果不想在SQL层拼接语句，你有两个变通思路：

• 客户端处理：在应用程序（比如C#、Python）中先查询所有权限列，再动态生成Pivot查询语句，最后执行。
• 报表工具处理：如果是用于报表展示，Power BI、Tableau等工具自带行转列功能，不需要手动写SQL。

但如果必须在SQL Server内部完成动态行转列，动态拼接SQL是唯一的原生解决方案。

内容的提问来源于stack exchange，提问作者s.blnc