为在不聚合的情况下将行放入列而编写的查询将使列变得混乱，而不是按顺序列出它们
为在不聚合的情况下将行放入列而编写的查询将使列变得混乱，而不是按顺序列出它们
提问于 2018-06-08 04:52:17
大约有1800个函数被转换为"pivot“的列，但它们没有按顺序调用，因此function_376总是先被调用，然后”随机“调用其他角色，并且并不是所有的角色都有那么多函数，它会拉取空值。如何让它按顺序拉取函数？
我正在尝试创建一个查询来生成一个结果集，该结果集可以很容易地以人类可读的方式复制并粘贴到Excel中。我的查询中的普通结果集提取了两列，角色和函数，每个不同的对对应一行。我的目标是将所有功能都放到同一行上，并为分配给角色的每个功能提供一列。我得到的最接近的结果是重用我在this answer中找到的脚本，但我遇到的问题是返回结果的查询使列变得杂乱无章。它没有在order行、function1、function2等中返回它们，因此结果中充满了NULL，这使得输出实际上无用。每次我运行@cols查询时，它都会以一致的顺序将函数放在一起，但它并不是按数字顺序排列的，它看起来是随机的。每个Function_N列代表与角色相关联的第N个函数，所以如果我可以让@cols查询按顺序构建，那么它就可以工作。
我如何重写这段代码，以使输出中的函数按数字顺序列出，从而使结果左对齐？
结果的代码和屏幕截图如下所示。
IF OBJECT_ID('tempdb.dbo.#roles', 'U') IS NOT NULL
DROP TABLE #roles;
CREATE TABLE #roles([role] VARCHAR(MAX), [function] VARCHAR(MAX))
Insert into #roles
select distinct r.r_desc, f.f_desc
from roles r
join role_functions rf on rf_rid = r_id
join Functions f on f_id = rf_fid
where r.r_Active = 'y'
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ','
+ QUOTENAME(rn)
from
(
select 'function_'+cast(row_number() over(partition by [role]
order by [role]) as varchar(20)) rn
from #roles
) src
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT [Role],' + @cols + ' from
(
select [Role], [function],
''function_''+cast(row_number() over(partition by [role]
order by [role]) as varchar(20)) rn
from #roles
) x
pivot
(
max([function])
for rn in (' + @cols + ')
) p '
execute(@query)
回答 1
Stack Overflow用户
发布于 2018-06-26 23:31:20
我解决了！独特的是在正确的方向上点头。我还需要另一个临时表来计算最大列数，然后我使用一个变量将其传递给第一个填充函数。如下所示：
IF OBJECT_ID('tempdb.dbo.#roles', 'U') IS NOT NULL
DROP TABLE #roles;
IF OBJECT_ID('tempdb.dbo.#funtcnt', 'U') IS NOT NULL
DROP TABLE #funtcnt;
CREATE TABLE #roles([role] VARCHAR(MAX), [function] VARCHAR(MAX))
Insert into #roles
select distinct r.r_desc, f.f_desc
from roles r
join role_functions rf on rf_rid = r_id
join Functions sf on f_id = rf_fid
where r.r_Active = 'y'
select [role], count([function]) as [count]
INTO #funtcnt
from #roles group by [role]
declare @topcount varchar(max) = (select top 1 [role] from #funtcnt order by [count] desc)
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ','
+ QUOTENAME(rn)
from
(
select 'function_'+cast(row_number() over(partition by [role]
order by [role]) as varchar(20)) rn
from #roles where [role] = @topcount
) src
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT [Role],' + @cols + ' from
(
select [Role], [function],
''function_''+cast(row_number() over(partition by [role]
order by [role]) as varchar(20)) rn
from #roles
) x
pivot
(
max([function])
for rn in (' + @cols + ')
) p '
execute(@query)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50749993
复制相关文章
相似问题
腾讯云开发者
