MySQL에서 행 레코드 결합하기
MySQL에서 여러 개의 행(row) 레코드를 하나의 행으로 결합하려면 CASE WHEN 개념과 함께 집계 함수인 MAX()를 활용하면 됩니다. 이 방식은 조건부 피벗(Pivot) 쿼리라고도 불리며, 국가별 직원 정보처럼 카테고리별 데이터를 열 형태로 변환할 때 매우 유용합니다.
먼저 예제에 사용할 테이블을 생성해 보겠습니다.
mysql> create table demo68
-> (
-> id int not null auto_increment primary key,
-> company_name varchar(50),
-> employee_name varchar(50),
-> country_name varchar(50)
-> );
Query OK, 0 rows affected (1.86 sec)
테스트 데이터 삽입
INSERT 명령어를 사용하여 테이블에 샘플 레코드를 추가합니다.
mysql> insert into demo68(company_name,employee_name,country_name)
values('Google','John','US');
Query OK, 1 row affected (0.29 sec)
mysql> insert into demo68(company_name,employee_name,country_name)
values('Google','Bob','UK');
Query OK, 1 row affected (0.10 sec)
mysql> insert into demo68(company_name,employee_name,country_name)
values('Google','David','AUS');
Query OK, 1 row affected (0.08 sec)
저장된 데이터 확인
SELECT 문을 사용하여 테이블의 레코드를 조회해 보겠습니다.
mysql> select *from demo68;
위 쿼리는 다음과 같은 결과를 출력합니다.
+----+--------------+---------------+--------------+
| id | company_name | employee_name | country_name |
+----+--------------+---------------+--------------+
| 1 | Google | John | US |
| 2 | Google | Bob | UK |
| 3 | Google | David | AUS |
+----+--------------+---------------+--------------+
3 rows in set (0.00 sec)
CASE WHEN을 활용한 행 결합 쿼리
다음은 MySQL에서 여러 행 레코드를 하나로 결합하는 쿼리입니다. 각 국가명을 조건으로 하는 CASE WHEN 표현식에 MAX() 함수를 적용하고, GROUP BY로 회사명별로 묶어줍니다.
mysql> select
-> company_name,
-> max(case when country_name= 'US' then employee_name end) as US_Employee_Name,
-> max(case when country_name= 'UK' then employee_name end) as UK_Employee_Name,
-> max(case when country_name= 'AUS' then employee_name end) as AUS_Employee_Name
-> from demo68
-> group by company_name;
위 쿼리는 다음과 같은 결과를 출력합니다.
+--------------+------------------+------------------+-------------------+
| company_name | US_Employee_Name | UK_Employee_Name | AUS_Employee_Name |
+--------------+------------------+------------------+-------------------+
| Google | John | Bob | David |
+--------------+------------------+------------------+-------------------+
1 row in set (0.05 sec)
쿼리 동작 원리
이 쿼리가 작동하는 방식을 살펴보면 다음과 같습니다.
1. CASE WHEN 조건 평가: country_name이 'US'인 행만 첫 번째 CASE 표현식에서 employee_name 값을 반환하고, 나머지 행은 NULL을 반환합니다. 마찬가지로 'UK'와 'AUS'에 대해서도 동일하게 적용됩니다.
2. MAX() 함수의 역할: 그룹 내에서 NULL이 아닌 값만 선택됩니다. 각 국가 조건에 해당하는 직원 이름은 하나뿐이므로, MAX()는 해당 값을 그대로 반환합니다.
3. GROUP BY로 묶기: company_name을 기준으로 그룹화하면 같은 회사의 여러 행이 하나의 행으로 결합되어, 국가별 직원 이름이 열 형태로 정렬된 결과를 얻을 수 있습니다.
이처럼 CASE WHEN과 집계 함수를 조합하면 별도의 피벗 기능 없이도 MySQL에서 손쉽게 행을 열로 변환하는 크로스탭(crosstab) 보고서를 만들 수 있습니다.