您可以在结果中添加一个额外的列，并在该列上使用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 Tpivot (min(dummy) for modulename in ([Module 1],[Module 2],[Module 3])) as P
如果要动态构建它，则需要首先执行查询，该查询返回结果中包含的模块，然后需要使用它来构建动态语句。最好将查询结果存储在临时表中，然后在构建动态查询时使用该表。
SELECt am.agentID AS agentid, pa.agentDisplayName agentdisplayname, m.ModuleName ModuleNameINTO #TmpFROM AgentModule AS am JOIN primaryagent AS pa ON am.agentID = pa.AgentID JOIN Module AS m ON am.ModuleID = m.ModuleIDWHERe 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 @SQLdrop table #Tmp
