您可以向结果中添加额外的列并在该列上使用 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