Output a matrix view from table

Question

I am newbie please help me, I have data in table with 4 column, something like this :

+---------------------+
| id  ah  av  score   |
+---------------------+
| 1   A   A    1      |
| 2   A   B    2      |
| 3   B   A   0.5     |
| 4   B   B   0.14    |
+---------------------+

I want to output the query result something like this

+-------------------+
| Type   A    B     |
+-------------------+
| A      1    2     |
| B      0.5  0.14  |
+-------------------+

my current code : SELECT ah, (CASE WHEN av = 'A' THEN score END) AS A, (CASE WHEN av = 'B' THEN score END ) AS B FROM ahp — samurai-cyber, Mar 19 '16 at 10:09
Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Shadow, Mar 19 '16 at 10:13

score 1 · Answer 1 · answered Mar 19 '16 at 10:03

1

Your problem requires a pivot table as you are 'pivoting' one of your columns to become a row.

First create a view with all the data you need (pretty much ditch the ID):

create view Scores_Simple as (
select
   ah as Type,
   case when av = "A" then Score end as A,
   case when av = "B" then Score end as B
from Scores
);

Then pivot the table:

create view Scores_Simple_Pivot as (
select
   Type,
   sum(A) as A,
   sum(B) as B
from Scores_Simple
group by Type
);

Tested here

answered Mar 19 '16 at 10:03

Mayura Vivekananda

674
4
15

Thank your for your Answer, If I could vote this up more than once, I would. Great, clear and concise explanation of a common issue that comes up, a solution to which I have rarely seen so well-explained ! – samurai-cyber Mar 19 '16 at 10:27
@neraikudai Well, you could always upvote it once. Note that there's no need for a view here. – Strawberry Mar 19 '16 at 11:21
@strawberry, thank you for more easier solution. – samurai-cyber Mar 19 '16 at 13:08
@Strawberry yea the view is not needed. Just makes it easier to visualize what is happening in the table, so you can apply this to more complex problems. – Mayura Vivekananda Mar 19 '16 at 22:03

score 1 · Accepted Answer · answered Mar 19 '16 at 11:27

SELECT ah type
     , MAX(CASE WHEN av = 'A' THEN score END) 'A'
     , MAX(CASE WHEN av = 'B' THEN score END) 'B' 
  FROM my_table 
 GROUP 
    BY ah;
+------+------+------+
| type | A    | B    |
+------+------+------+
| A    | 1.00 | 2.00 |
| B    | 0.50 | 0.14 |
+------+------+------+

Output a matrix view from table

2 Answers2