행과 열 바꾸기

mysql> select * from test_row;
+-----------+----------+
| colHeader | colValue |
+-----------+----------+
|  Header1  |  value1  |
|  Header2  |  value2  |
|  Header3  |  value3  |
+-----------+----------+
 
mysql> select
    -> t1.colValue as "Header1",
    -> t2.colValue as "Header2",
    -> t3.colValue as "Header3"
    -> from
    -> (select * from test_row where colHeader = 'Header1') t1
    -> join (select * from test_row where colHeader = 'Header2') t2
    -> join (select * from test_row where colHeader = 'Header3') t3;
+---------+---------+---------+
| Header1 | Header2 | Header3 |
+---------+---------+---------+
|  value1 | value2  | value3  |
+---------+---------+---------+
 
 
 
 
mysql> select * from score;
+-----+------+-------+-------+
| idx | name | class | score |
+-----+------+-------+-------+
|   1 | choi | kor   |    90 |
|   2 | choi | eng   |    80 |
|   3 | choi | math  |    70 |
|   4 | kim  | kor   |    60 |
|   5 | kim  | eng   |    85 |
|   6 | kim  | math  |   100 |
+-----+------+-------+-------+
 
mysql> select name,
    -> case when class = 'kor' then score end as K,
    -> case when class = 'eng' then score end as E,
    -> case when class = 'math' then score end as M
    -> from score;
+------+------+------+------+
| name |  K   |  E   | M    |
+------+------+------+------+
| choi |   90 | NULL | NULL |
| choi | NULL |   80 | NULL |
| choi | NULL | NULL |   70 |
| kim  |   60 | NULL | NULL |
| kim  | NULL |   85 | NULL |
| kim  | NULL | NULL |  100 |
+------+------+------+------+
 
mysql> select name "이름", sum(K) "국어", sum(E) "영어", sum(M) "수학" from
    -> (
    -> select name,
    -> case when class = 'kor' then score end as K,
    -> case when class = 'eng' then score end as E,
    -> case when class = 'math' then score end as M
    -> from score
    -> )
    -> as temp_table_name group by name;
+------+------+------+------+
| 이름 |  국어 | 영어 | 수학 |
+------+------+------+------+
| choi |   90 |   80 |   70 |
| kim  |   60 |   85 |  100 |
+------+------+------+------+