title: MySQL Pivot table using Join
body: <pre><code>Hey guys I am stuck at a problem in MySQL, can you please help me.
  </code></pre>
  
  <p>I have a form which gives ratings to certain parameters. Now what i want to do is i want to show a table in which for every LocationID all the parameter values should be shown in a single row.<br>
  This is my first table named interviewmaster where the records are stored</p>
  
  <pre><code>InterviewID     InterviewDate           IntervieweeID  SourceID   LocationID 
  1           2013-06-10 16:11:09              1            1        3    
  2           2013-06-10 16:12:19              2            2        2    
  3           2013-06-10 16:13:05              3            5        5    
  4           2013-06-10 16:13:46              4            4        6    
  5           2013-06-10 16:14:35              5            2        1    
  6           2013-06-10 16:15:26              6            3        7    
  7           2013-06-10 16:16:05              7            5        8    
  8           2013-06-10 16:16:51              8            1        4    
  9           2013-06-10 16:22:31              9            5        3    
  10          2013-06-11 08:51:20             10            1        1    
  </code></pre>
  
  <p>My second table is interviewparameters</p>
  
  <pre><code>ParameterID   Parameter
  1            The Welcome
  2            Quality Of Service
  3            Speed Of Service
  4            Staff Attitude
  5            Quality of food/beverage
  6            Overall value for money
  7            Ambience
  8            Overall Experience
  </code></pre>
  
  <p>My third table is interviewratings</p>
  
  <pre><code>InterviewID parameterID Rating
  1                  1    5
  1                  2    4
  1                  3    5
  1                  4    4
  1                  5    5
  1                  6    4
  1                  7    5
  2                  1    5
  2                  2    5
  2                  3    5
  2                  4    5
  2                  5    5
  2                  6    4
  2                  7    5
  3                  1    4
  3                  2    3
  3                  3    4
  3                  4    3
  3                  5    4
  3                  6    3
  3                  7    4
  4                  1    2
  4                  2    2
  4                  3    2
  4                  4    2
  4                  5    2
  4                  6    2
  4                  7    2
  </code></pre>
  
  <p>Now the query that i am using is</p>
  
  <pre><code>select LocationID,
  (CASE when ParameterID=1  then avg(interviewratings.Rating) else null end) as Welcome,
  (CASE when ParameterID=2  then avg(interviewratings.Rating) else null end) as Service,
  (CASE when ParameterID=3  then avg(interviewratings.Rating) else null end) as Speed,
  (CASE when ParameterID=4  then avg(interviewratings.Rating) else null end) as Quality,
  (CASE when ParameterID=5  then avg(interviewratings.Rating) else null end) as Overall,
  (CASE when ParameterID=6  then avg(interviewratings.Rating) else null end) as Ambience,
  (CASE when ParameterID=7  then avg(interviewratings.Rating) else null end) as Experience
  from interviewratings join interviewmaster on
  interviewratings.InterviewID=interviewmaster.InterviewID 
  group by ParameterID, LocationID 
  </code></pre>
  
  <p>This gives the result as</p>
  
  <pre><code>LocationID    welcome   source   speed   quality   overall   Ambiance   experience
  1     2.8333                         
  2     3.0000                        
  3     3.4000                        
  4     2.5000                        
  5     2.3333                        
  6     2.8000                        
  7     2.8182                        
  8     4.0000                        
  1             3.0000                    
  2             2.5714                    
  3             3.2000                    
  4             3.3333                    
  5             2.6667                    
  6             2.8000                    
  7             2.3636                    
  8             4.3333                    
  1                     3.0000                
  2                     2.5714                
  3                     3.4000                
  4                     2.8333                
  5                     3.8889                
  6                     2.8000                
  7                     2.7273                
  8                     3.3333                
  1                              3.0000           
  2                              4.2857           
  3                              3.2000           
  4                              3.5000           
  5                              2.7778           
  6                              2.8000           
  7                              3.0000           
  8                              3.0000           
  1                                        3.0000     
  2                                        2.8571     
  3                                        3.4000     
  4                                        2.8333     
  5                                        3.6667     
  6                                        2.8000     
  7                                        3.1818     
  8                                        2.6667     
  1                                                 3.0000    
  2                                                 2.4286    
  3                                                 3.2000    
  4                                                 2.8333    
  5                                                 2.6667    
  6                                                 3.6000    
  7                                                 3.0909    
  8                                                 3.0000    
  1                                                        3.0000
  2                                                        2.8571
  3                                                        3.8000
  4                                                        2.5000
  5                                                        2.6667
  6                                                        2.8000
  7                                                        3.0909
  8                                                        3.3333
  </code></pre>
  
  <p>Location table is </p>
  
  <pre><code>LocationID  Location
  1      Seasonal Tastes
  2      Daily Treats
  3      Eest
  4      Prego
  5      Mix
  6      Splash
  7      Xiao chi
  8      The Living Room
  </code></pre>
  
  <p>I want my data to be like this</p>
  
  <pre><code>Location  Welcome  Service  Speed   Quality Overall Ambience  Experience
   seasonal     2.8333   3.0000   3.0000  3.0000  3.0000  3.0000    3.0000            
   Daily treats 3.0000   2.5714   2.5714  4.2857  2.8571  2.4286    2.8571            
   Eest         3.4000   3.2000   3.4000  3.2000  3.4000  3.2000    3.8000           
   Prego        2.5000   3.3333   2.8333  3.5000  2.8333  2.8333    2.5000                        
   Mix          2.3333   2.6667   3.8889  2.7778  3.6667  2.6667    2.6667            
   Splash       2.8000   2.8000   2.8000  2.8000  2.8000  3.6000    2.8000                  
   Xiao chi     2.8182   2.3636   2.7273  3.0000  3.1818  3.0909    3.0909               
   The Living   4.0000   4.3333   3.3333  3.0000  2.6667  3.0000    3.3333               
  </code></pre>
  
  <p>and all the results in all the columns within these 8 rows.. can you tell me how to do it ?</p>