title: how to use SQL table rows as columns for another table
body: <p>I have one table of activities, one table of users, and a third table linking users to activities using foreign keys.</p>
  
  <p>What I'm trying to do is create a results table that will have the activities as columns and the users as rows with the cells being the number of activities of that type the user participated in.</p>
  
  <p>For example, the columns would be</p>
  
  <pre><code>User | Activity A | Activity B | Activity C
  </code></pre>
  
  <p>And a user who had done each activity three times would result in a row of</p>
  
  <pre><code>John Doe | 3 | 3 | 3
  </code></pre>
  
  <p>Now, I can do this easily if I manually add a count() call for each activity in the database like:</p>
  
  <pre><code>select 
      u.name, 
      (select count(*) 
       from userActivity ua 
       where ua.userID = user.userID and ua.activityID = 1), 
      (select count(*) 
       from userActivity ua 
       where ua.userID = user.userID and ua.activityID = 2),
      (select count(*) 
       from userActivity ua 
       where ua.userID = user.userID and ua.activityID = 3) 
  from 
      user u
  </code></pre>
  
  <p>But this doesn't help me if someone enters an Activity D into the system tomorrow. The report wouldn't show it. How can I use the Activity table's rows as columns?</p>