SQL Server PIVOT 函数 [英] SQL Server PIVOT Function
问题描述
我有一个检索所有代理及其模块的查询,结果集将为每个模块返回 1 行.
SELECTam.agentID AS agentid,pa.agentDisplayName 代理显示名称,m.ModuleName 模块名从代理模块 AS加入主代理AS paON am.agentID = pa.AgentIDJOIN 模块 AS mON am.ModuleID = m.ModuleID在哪里米.活跃 = 1AND pa.groupID = 75
数据集返回如下
<前>代理ID |代理显示名称 |模块名94 |代理1 |模块一94 |代理1 |模块 294 |代理1 |模块 323 |代理1 |模块 223 |代理1 |模块 3我正在尝试使用 PIVOT 函数返回一个看起来更像
的表<前>代理ID |代理显示名称 |模块 1 |模块 2 |模块 3 |.. .. ..94 |代理1 |1 |1 |123 |代理2 |0 |1 |1有一个动态模块列表,因此我无法在查询中对它们进行硬编码.我已经尝试过 PICOT,但它似乎需要一个聚合函数,并且不太确定这是我在这种情况下需要的.
您可以向结果中添加额外的列并在该列上使用 min().结果将是 1
或 null
.使用 isnull
获得 0
而不是 null
.
选择agentid,代理显示名称,isnull([模块 1], 0) 为 [模块 1],isnull([模块 2], 0) 为 [模块 2],isnull([模块 3], 0) 作为 [模块 3]从(选择 agentid, agentdisplayname, modulename, 1 as dummy来自你的结果集) 作为 T枢(min(dummy) for modulename in ([Module 1],[Module 2],[Module 3])) 作为 P
如果你想动态地构建它,你需要先做一个查询,返回你在结果中拥有的模块,然后你需要使用它来构建动态语句.您最好将查询的结果存储在临时表中,然后在构建动态查询时使用该表.
SELECTam.agentID AS agentid,pa.agentDisplayName 代理显示名称,m.ModuleName 模块名进入#Tmp从代理模块 AS加入主代理AS paON am.agentID = pa.AgentIDJOIN 模块 AS mON am.ModuleID = m.ModuleID在哪里米.活跃 = 1AND pa.groupID = 75
使用 #Tmp
构建并运行动态查询.
声明@FieldList1 nvarchar(max)声明 @FieldList2 nvarchar(max)声明@SQL nvarchar(max)设置@FieldList1 =(select ',isnull('+quotename(modulename)+', 0) as '+quotename(modulename)来自#Tmp按模块名分组按模块名排序对于 xml path(''), type).value('.', 'nvarchar(max)')设置@FieldList2 = 东西((选择 ','+quotename(modulename)来自#Tmp按模块名分组按模块名排序对于 xml path(''), type).value('.', 'nvarchar(max)') , 1, 1, '')设置@SQL ='选择agentid,agentdisplayname'+@FieldList1+'from (选择 agentid, agentdisplayname, modulename, 1 as dummy来自 YourTable) 作为 T枢轴 (min(dummy) for modulename in ('+@FieldList2+')) 作为 P'exec sp_executesql @SQL删除表#Tmp
I have a query that retrieves all agents and thier modules, the result set will return 1 row per module.
SELECT
am.agentID AS agentid,
pa.agentDisplayName agentdisplayname,
m.ModuleName ModuleName
FROM
AgentModule AS am
JOIN primaryagent AS pa
ON am.agentID = pa.AgentID
JOIN Module AS m
ON am.ModuleID = m.ModuleID
WHERE
m. Active = 1
AND pa.groupID = 75
Dataset is return as below
agentid | agentdisplayname | modulename 94 | Agent1 | Module 1 94 | Agent1 | Module 2 94 | Agent1 | Module 3 23 | Agent1 | Module 2 23 | Agent1 | Module 3
I am trying to use the PIVOT function to return a table that looks more like
agentid | agentdisplayname | Module 1 | Module 2 | Module 3 |.. .. .. 94 | Agent1 | 1 | 1 | 1 23 | Agent2 | 0 | 1 | 1
There are a dynamic list of modules so I cannot hard code them in the query. I have tried PICOT but it seems to expect an aggregate function and not quite sure it is what I would need for this scenario.
You can add a extra column to you result and use min() on that column. The result will be 1
or null
. Use isnull
to get a 0
instead of null
.
select agentid,
agentdisplayname,
isnull([Module 1], 0) as [Module 1],
isnull([Module 2], 0) as [Module 2],
isnull([Module 3], 0) as [Module 3]
from
(
select agentid, agentdisplayname, modulename, 1 as dummy
from YourResultset
) as T
pivot
(min(dummy) for modulename in ([Module 1],[Module 2],[Module 3])) as P
If you want to build this dynamically you need to first do a query that returns the modules you have in the result and then you need to use that to build the dynamic statement. It is probably best for you to store the result from your query in a temp table and then use that table when you build your dynamic query.
SELECT
am.agentID AS agentid,
pa.agentDisplayName agentdisplayname,
m.ModuleName ModuleName
INTO #Tmp
FROM
AgentModule AS am
JOIN primaryagent AS pa
ON am.agentID = pa.AgentID
JOIN Module AS m
ON am.ModuleID = m.ModuleID
WHERE
m. Active = 1
AND pa.groupID = 75
Build and run the dynamic query using #Tmp
.
declare @FieldList1 nvarchar(max)
declare @FieldList2 nvarchar(max)
declare @SQL nvarchar(max)
set @FieldList1 =
(select ',isnull('+quotename(modulename)+', 0) as '+quotename(modulename)
from #Tmp
group by modulename
order by modulename
for xml path(''), type).value('.', 'nvarchar(max)')
set @FieldList2 = stuff(
(select ','+quotename(modulename)
from #Tmp
group by modulename
order by modulename
for xml path(''), type).value('.', 'nvarchar(max)') , 1, 1, '')
set @SQL =
'select agentid, agentdisplayname'+@FieldList1+
'from (select agentid, agentdisplayname, modulename, 1 as dummy
from YourTable) as T
pivot (min(dummy) for modulename in ('+@FieldList2+')) as P'
exec sp_executesql @SQL
drop table #Tmp
这篇关于SQL Server PIVOT 函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!