횡으로 구성되어있는 데이터를 종으로 만드는 방법에 대해 설명합니다. 먼저 전, 후의 데이터를 비교해보세요.
처리 전 데이터
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