title: How can I dynamically create columns in SQL select statement
body: <p>I have 3 tables. Team, Option, OptionTeam.<br>
  The Team holds a TeamId and Name<br>
  Option holds OptionId, OptionGroup<br>
  OptionTeam holds TeamId, OptionId, OptionGroup  </p>
  
  <pre><code>select a.TeamId, a.Name
  (select count(*) from OptionTeam ot where ot.TeamId=a.TeamId and ot.OptionGroup=4) as Option1,
  (select count(*) from OptionTeam ot where ot.TeamId=a.TeamId and ot.OptionGroup=5) as Option2,
  (select count(*) from OptionTeam ot where ot.TeamId=a.TeamId and ot.OptionGroup=6) as Option3,
  (select count(*) from OptionTeam ot where ot.TeamId=a.TeamId and ot.OptionGroup=11) as Option4
  from Team a 
  </code></pre>
  
  <p>I want to get a list of Teams, and extra columns indicating how many options of each group are connected to each Team. This is done by the above query, but I want to replace the 4,5,6,11 with values of OptionGroup from a table Option.<br>
  It has to be dynamic, because there might be a new OptionGroup in the future, and I want the stored procedure to be able to handle it.</p>
  
  <p>Sample data:  </p>
  
  <pre><code>Team  
  TeamId  
  1  
  2  
  3  
  </code></pre>
  
  <p>Option  </p>
  
  <pre><code>OptionId | OptionGroup  
  11 | 4  
  12 | 5  
  13 | 4  
  14 | 4  
  15 | 5  
  </code></pre>
  
  <p>OptionTeam  </p>
  
  <pre><code>TeamId | OptionId | OptionGroup  
  1 | 11 | 4  
  1 | 13 | 4  
  2 | 12 | 5  
  2 | 14 | 4  
  3 | 15 | 5  
  </code></pre>
  
  <p>And the list I want to get is  </p>
  
  <pre><code>TeamId | Group4 (OptionGroup=4) | Group5 (OptionGroup=5)  
  1 | 2 | 0  
  2 | 1 | 1  
  3 | 0 | 1  
  </code></pre>