SQL Server PIVOT 函数 [英] SQL Server PIVOT Function

查看:62
本文介绍了SQL Server PIVOT 函数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个检索所有代理及其模块的查询,结果集将为每个模块返回 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().结果将是 1null.使用 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆