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

数据集返回如下

代理ID |代理显示名称 |模块名
94 |代理1 |模块 1
94 |代理1 |模块 2
94 |代理1 |模块 3
23 |代理1 |模块 2
23 |代理1 |模块 3

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

代理ID |代理显示名称 |模块 1 |模块 2 |模块 3 |.. .. ..
94 |代理1 | 1 | 1 | 1
23 |代理2 | 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