SQL Server PIVOT功能

Question

我有一个查询，它检索所有代理和这些模块，结果集将为每个模块返回1行。

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

数据集返回如下

agentid  |  agentdisplayname  |  modulename
94       |  Agent1            |  Module 1
94       |  Agent1            |  Module 2
94       |  Agent1            |  Module 3
23       |  Agent1            |  Module 2
23       |  Agent1            |  Module 3

我正在尝试使用PIVOT函数返回看起来更像的表

agentid  |  agentdisplayname  |  Module 1  |  Module 2  |  Module 3  |.. .. .. 
94       |  Agent1            |  1         |  1         |  1
23       |  Agent2            |  0         |  1         |  1 

有动态的模块列表，因此我无法在查询中对它们进行硬编码。我尝试了PICOT，但似乎期望使用聚合函数，但不太确定这是我在此情况下所需的功能。

Answer 1

您可以在结果中添加一个额外的列，并在该列上使用min（）。结果将为1或null。用isnull得到0，而不是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

如果要动态构建它，则需要首先执行查询，该查询返回结果中包含的模块，然后需要使用它来构建动态语句。最好将查询结果存储在临时表中，然后在构建动态查询时使用该表。

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

使用构建并运行动态查询#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