SQL JOIN multiple tables with PIVOT

Question

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:

    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

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.

I followed along with SQL server join tables and pivot but could not get it to work with more than 2 tables with not all Queries needed to be PIVOT'ed.

Welcome to Stack Overflow, @Mikey. Please edit your question to add additional information to clarify what you are asking. What is your current query? It is unclear if "Final Table" represents the current result of your query or your desired result. Please have both in your question. — Degan, Aug 18 '17 at 17:46

score 0 · Answer 1 · answered Aug 18 '17 at 18:24

you only need to Pivot table 3 - so just pivot Table 3 with a CTE expression as below and then join the result with Table 1 and 2


WITH pivot_cte 
     ( 
          id, 
          query_a, 
          query_b 
     ) 
     AS 
     -- Define the CTE query. 
     ( 
            SELECT * 
            FROM   ( 
                          SELECT Id, a query_a, 
                                 b query_b 
                          FROM   table3 ) table3 PIVOT ( Max(response) FOR query IN ([a], 
                                                                                     [b]) ) piv;)
     SELECT * 
     FROM   pivot_cte 
     JOIN   table1 
     ON     table1.id=pivot_cte.id 
     JOIN   table2 
     ON     table2.id=pivot_cte.id

SQL JOIN multiple tables with PIVOT

1 Answers1