title: SQL JOIN multiple tables with PIVOT
body: <p>I'm trying to INNER JOIN 3 tables together with a PIVOT on the third table.  Joining isn't an issue but PIVOT's are pretty new to me and combining them together has turned into a disaster.  I'm not sure how to do the following:</p>
  
  <pre><code>    Table 1
  ID  Mnemonic    Gender
  1   SKYL        F
  2   HANS        M
  
      Table 2
  ID  LastName    FirstName
  1   Skywalker   Leia
  2   Solo        Han
  
      Table 3
  ID  Query   Response
  1   a       red
  1   b       green
  1   c       blue
  1   d       yellow
  2   a       black
  2   b       white
  2   c       orange
  2   d       purple
  
                          Final Table
  ID  Mnemonic    Gender  LastName    FirstName   Query_a     Query_b
  1   SKYL        F       Skywalker   Leia        red         green
  2   HANS        M       Solo        Han         black       white
  </code></pre>
  
  <p>One area I know I'm having issues is that I dont want all the 'Responses' from Table 3, just when the 'Query' column is 'a' or 'b'. I might end up creating 2 new tables to replace Table 3, so I can get rid of the PIVOT all together and just INNER JOIN the 4 tables.</p>
  
  <p>I followed along with <a href="https://stackoverflow.com/questions/17073134/sql-server-join-tables-and-pivot">SQL server join tables and pivot</a> but could not get it to work with more than 2 tables with not all Queries needed to be PIVOT'ed.</p>