Ashish Batra
Reputation: 63
MySQL Pivot table using Join
Hey guys I am stuck at a problem in MySQL, can you please help me.
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.
This is my first table named interviewmaster where the records are stored
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
My second table is interviewparameters
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
My third table is interviewratings
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
Now the query that i am using is
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
This gives the result as
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
Location table is
LocationID Location
1 Seasonal Tastes
2 Daily Treats
3 Eest
4 Prego
5 Mix
6 Splash
7 Xiao chi
8 The Living Room
I want my data to be like this
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
and all the results in all the columns within these 8 rows.. can you tell me how to do it ?
Upvotes: 1
Views: 1793
Answers (2)
peterm
Reputation: 92795
Try
SELECT l.Location,
AVG(CASE WHEN r.ParameterID=1 THEN r.Rating ELSE NULL END) Welcome,
AVG(CASE WHEN r.ParameterID=2 THEN r.Rating ELSE NULL END) Service,
AVG(CASE WHEN r.ParameterID=3 THEN r.Rating ELSE NULL END) Speed,
AVG(CASE WHEN r.ParameterID=4 THEN r.Rating ELSE NULL END) Quality,
AVG(CASE WHEN r.ParameterID=5 THEN r.Rating ELSE NULL END) Overall,
AVG(CASE WHEN r.ParameterID=6 THEN r.Rating ELSE NULL END) Ambience,
AVG(CASE WHEN r.ParameterID=7 THEN r.Rating ELSE NULL END) Experience
FROM interviewmaster i JOIN Location l
ON i.LocationID = l.LocationID JOIN interviewratings r
ON i.InterviewID = r.InterviewID
GROUP BY i.LocationID, l.Location
Here is SQLFiddle demo
Upvotes: 1
Stephan
Reputation: 8090
Try this:
select
l.Location,
sum(CASE when r.ParameterID=1 then r.Rating else 0 end)/sum(CASE when r.ParameterID=1 then 1 else 0 end) as Welcome,
sum(CASE when r.ParameterID=2 then r.Rating else 0 end)/sum(CASE when r.ParameterID=2 then 1 else 0 end) as Service,
sum(CASE when r.ParameterID=3 then r.Rating else 0 end)/sum(CASE when r.ParameterID=3 then 1 else 0 end) as Speed,
sum(CASE when r.ParameterID=4 then r.Rating else 0 end)/sum(CASE when r.ParameterID=4 then 1 else 0 end) as Quality,
sum(CASE when r.ParameterID=5 then r.Rating else 0 end)/sum(CASE when r.ParameterID=5 then 1 else 0 end) as Overall,
sum(CASE when r.ParameterID=6 then r.Rating else 0 end)/sum(CASE when r.ParameterID=6 then 1 else 0 end) as Ambience,
sum(CASE when r.ParameterID=7 then r.Rating else 0 end)/sum(CASE when r.ParameterID=7 then 1 else 0 end) as Experience
from
interviewratings r
join interviewmaster m
on m.InterviewID = r.InterviewID
join Location l
on l.LocationID = m.LocationID
group by
m.LocationID
Upvotes: 1
Related Questions
- MySQL join tables and pivot (one more)
- MySql : how to join this two tables with multiple pivot
- MySQL double join / pivot table
- join pivot table in mysql
- Join to Pivot Table Mysql
- Joining Tables and Pivoting Data with MySQL
- How to Join and Pivot a Table in MySQL
- MySQL pivot table and JOINS
- MySQL Query and Pivot Tables
- mysql join query