SQL Server 2017及以上版本
STRING_AGG
确实，STRING_AGG 是 SQL Server 2017 (版本 14.x) 及更高版本中引入的一个聚合函数，它允许你将多个行的值组合成一个字符串，并且你可以指定一个分隔符来分隔这些值。由于你正在使用 SQL Server 2014，STRING_AGG 函数在你的环境中是不可用的。
在 SQL Server 2014 中，要达到类似 STRING_AGG 的效果，你可以使用 FOR XML PATH 方法，这是 SQL Server 较早版本中常用的技巧来聚合字符串。下面是一个使用 FOR XML PATH 来模拟 STRING_AGG 功能的示例：
DECLARE @cols AS NVARCHAR(MAX);SELECT @cols = STUFF((SELECT ',' + QUOTENAME(FNUMBER)FROM (SELECT DISTINCT FNUMBERFROM YourTable -- 替换为你的实际表名) AS SubQueryFOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); -- 移除第一个逗号-- 现在 @cols 包含了用逗号分隔的、带引号的 FNUMBER 列表
在这个示例中，STUFF 函数用于移除由 FOR XML PATH 生成的字符串列表的第一个逗号。QUOTENAME 函数用于确保列名被正确地用方括号括起来，这在处理包含特殊字符或保留字的列名时特别有用。
一旦你有了这个列表，你就可以像之前的示例那样构建你的动态 PIVOT 查询。
示例
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); -- 获取所有唯一的FNUMBER值，并构建用于PIVOT操作的列列表 SELECT @cols = STRING_AGG(QUOTENAME(FNUMBER), ',') FROM ( SELECT DISTINCT r1.FNUMBER FROM t_sec_FuncPermission p1 INNER JOIN t_sec_funcPermissionEntry p2 ON p1.FItemID = p2.FItemID INNER JOIN T_SEC_PERMISSIONITEM p3 ON p2.FPermissionItemID = p3.FItemID INNER JOIN t_meta_objectType o1 ON p1.FObjectTypeID = o1.FID AND o1.fdevtype != 2 INNER JOIN t_sec_role r1 ON p1.FRoleID = r1.FRoleID Where 1=1 ) AS RoleNumbers; -- 构建动态PIVOT查询 SET @query = ' SELECT FObjectTypeID, FItemID, ' + @cols + ' FROM ( SELECT r1.FNUMBER, p1.FObjectTypeID, p3.FItemID, CASE p2.FPERMISSIONSTATUS WHEN ''0'' THEN ''有权'' WHEN ''2'' THEN ''禁止'' ELSE ''无权'' END AS FPermissionStatusName FROM t_sec_FuncPermission p1 INNER JOIN t_sec_funcPermissionEntry p2 ON p1.FItemID = p2.FItemID INNER JOIN T_SEC_PERMISSIONITEM p3 ON p2.FPermissionItemID = p3.FItemID INNER JOIN t_meta_objectType o1 ON p1.FObjectTypeID = o1.FID AND o1.fdevtype != 2 INNER JOIN t_sec_role r1 ON p1.FRoleID = r1.FRoleID Where 1=1 ) x PIVOT ( MAX(FPermissionStatusName) FOR FNUMBER IN (' + @cols + ') ) p '; -- 执行动态查询 EXEC sp_executesql @query;
SQL Server 2014以下版本
在 SQL Server 2014 中，STRING_AGG 函数是不可用的，因为这是一个在 SQL Server 2017（版本 14.x）及更高版本中引入的聚合函数。为了在 SQL Server 2014 中实现类似的功能，你可以使用 FOR XML PATH 方法来生成一个由逗号分隔的字符串列表。
以下是将你的查询修改为适用于 SQL Server 2014 的版本：
DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX);-- 获取所有唯一的FNUMBER值，并构建用于PIVOT操作的列列表
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(FNUMBER)FROM (SELECT DISTINCT r1.FNUMBERFROM t_sec_FuncPermission p1INNER JOIN t_sec_funcPermissionEntry p2 ON p1.FItemID = p2.FItemIDINNER JOIN T_SEC_PERMISSIONITEM p3 ON p2.FPermissionItemID = p3.FItemIDINNER JOIN t_meta_objectType o1 ON p1.FObjectTypeID = o1.FID AND o1.fdevtype != 2INNER JOIN t_sec_role r1 ON p1.FRoleID = r1.FRoleID-- Where 1=1 这个条件实际上没有作用，可以移除) AS RoleNumbersFOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); -- 使用STUFF函数移除最前面的逗号-- 构建动态PIVOT查询
SET @query = '
SELECT FObjectTypeID, FItemID, ' + @cols + '
FROM (SELECT r1.FNUMBER,p1.FObjectTypeID,p3.FItemID,CASE p2.FPERMISSIONSTATUSWHEN ''0'' THEN ''有权''WHEN ''2'' THEN ''禁止''ELSE ''无权''END AS FPermissionStatusNameFROM t_sec_FuncPermission p1INNER JOIN t_sec_funcPermissionEntry p2 ON p1.FItemID = p2.FItemIDINNER JOIN T_SEC_PERMISSIONITEM p3 ON p2.FPermissionItemID = p3.FItemIDINNER JOIN t_meta_objectType o1 ON p1.FObjectTypeID = o1.FID AND o1.fdevtype != 2INNER JOIN t_sec_role r1 ON p1.FRoleID = r1.FRoleID-- Where 1=1 这个条件实际上没有作用，可以移除
) x
PIVOT (MAX(FPermissionStatusName) FOR FNUMBER IN (' + @cols + ')
) p';-- 执行动态查询
EXEC sp_executesql @query;
在这个修改后的版本中，我使用了 FOR XML PATH 来生成一个 XML 字符串，然后通过 .value('.', 'NVARCHAR(MAX)') 方法将其转换为 NVARCHAR(MAX) 类型。接着，我使用 STUFF 函数来移除字符串最前面的逗号，从而得到一个由逗号分隔的列名列表。
请注意，我在查询中移除了 Where 1=1 条件，因为它实际上并不对查询结果产生任何影响。如果你需要添加特定的过滤条件，你应该在 INNER JOIN 子句之前或之后添加它们。
