Posted
Filed under Mysql
종 데이터를 횡 데이트로 만들기 (세로 - 가로)

횡으로 구성되어있는 데이터를 종으로 만드는 방법에 대해 설명합니다. 먼저 전, 후의 데이터를 비교해보세요.

처리 전 데이터

1
2
3
4
5
6
7
8
9
10
+-----+------+-------+-------+
| idx | name | class | score |
+-----+------+-------+-------+
|   7 | choi | kor   |    90 |
|   8 | choi | eng   |    80 |
|   9 | choi | math  |    70 |
|  10 | kim  | kor   |    60 |
|  11 | kim  | eng   |    85 |
|  12 | kim  | math  |   100 |
+-----+------+-------+-------+

처리 후 데이터

1
2
3
4
5
6
+------+------+------+------+
| name | kor  | eng  | math |
+------+------+------+------+
| choi |   90 |   80 |   70 |
| kim  |   60 |   85 |  100 |
+------+------+------+------+

처리 후 데이터를 보면 name 기준으로 kor, eng, math항목이 횡으로 나열된 것을 볼 수 있습니다.

종 데이터 샘플 생성

종 데이터로 사용할 사용할 가상의 샘플 데이터를 생성해 보겠습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `score` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `class` varchar(32) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`idx`)
);
 
INSERT INTO score (name, class, score) VALUES ('choi', 'kor', 90);
INSERT INTO score (name, class, score) VALUES ('choi', 'eng', 80);
INSERT INTO score (name, class, score) VALUES ('choi', 'math', 70);
 
INSERT INTO score (name, class, score) VALUES ('kim', 'kor', 60);
INSERT INTO score (name, class, score) VALUES ('kim', 'eng', 85);
INSERT INTO score (name, class, score) VALUES ('kim', 'math', 100);

종 데이터 생성이 완료 되었습니다.

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM score;
+-----+------+-------+-------+
| idx | name | class | score |
+-----+------+-------+-------+
|   7 | choi | kor   |    90 |
|   8 | choi | eng   |    80 |
|   9 | choi | math  |    70 |
|  10 | kim  | kor   |    60 |
|  11 | kim  | eng   |    85 |
|  12 | kim  | math  |   100 |
+-----+------+-------+-------+

alias를 이용한 컬럼 생성

종 데이터를 kor, eng, math로 구성된 횡 데이터로 만들기 위해 alias를 이용 K, E, M 컬럼을 생성하고 kor데이터는 K에 eng데이터는 E에 math데이터는 M에 넣습니다.

값이 저장되지 않은 컬럼은 NULL이 저장됩니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 |
+------+------+------+------+

아직까지는 종 데이터의 형식이지만 이제 횡데이터로 만들기 위한 준비는 되었습니다.

횡 데이터 생성

위에서 만들었던 쿼리를 이용해서 임의의 테이블을 만듭니다.
임의의 테이블을 group by하고 alias를 이용해서 생성한 K, E, M 컬럼을 이용 횡데이터로 만듭니다.
K, E, M컬럼은 SUM 함수를 이용해서 더해주고 alias를 이용 kor, eng, math로 컬럼명을 지정합니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT name, SUM(K) as kor, SUM(E) as eng, SUM(M) as math 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 T GROUP BY name;
 
+------+------+------+------+
| name | kor  | eng  | math |
+------+------+------+------+
| choi |   90 |   80 |   70 |
| kim  |   60 |   85 |  100 |
+------+------+------+------+


[원문]http://blog.devez.net/307

참고 URL

http://stackoverflow.com/questions/1241178/mysql-rows-to-columns

2018/01/09 10:57 2018/01/09 10:57

blog.visualp.com

blog.visualp.com