title: SQL Server PIVOT Function
body: <p>I have a query that retrieves all agents and thier modules, the result set will return 1 row per module. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Dataset is return as below </p>
  
  <pre>
  agentid  |  agentdisplayname  |  modulename
  94       |  Agent1            |  Module 1
  94       |  Agent1            |  Module 2
  94       |  Agent1            |  Module 3
  23       |  Agent1            |  Module 2
  23       |  Agent1            |  Module 3
  </pre>
  
  <p>I am trying to use the PIVOT function to return a table that looks more like </p>
  
  <pre>
  agentid  |  agentdisplayname  |  Module 1  |  Module 2  |  Module 3  |.. .. .. 
  94       |  Agent1            |  1         |  1         |  1
  23       |  Agent2            |  0         |  1         |  1
  </pre>
  
  <p>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. </p>