[MYSQL] SQL 쿼리 결과 Pivoting 하기 (1) - CASE WHEN

SQL 등을 작성하여 쿼리 결과를 가져올 때,
가로로 놓여지는 데이터(행데이터)를 세로(열데이터)로 변환할 필요가 생길 수 있다.

예시를 들어보자.
아래와 같이 간단한 User 테이블이 있다.

create table user (
 	id int unsigned not null,
 	username varchar(50) not null,
 	primary key (id)
);

그리고 각 유저의 역할을 나타내는 UserRole 테이블이 있다.
각 유저의 역할은 enum으로 3가지가 있다고 가정했다(POST_MANAGER, SHOP_MANAGER, HR_MANAGER).

create table UserRole (
	userId int unsigned not null,
    role enum('POST_MANAGER', 'SHOP_MANAGER', 'HR_MANAGER') not null,
    foreign key (userId) references user(id)
);

User 테이블에는 Tom, Jerry, Happy 가 있고

INSERT INTO user(id, username) VALUES (1, 'Tom') , (2, 'Jerry'), (3, 'Happy');
    

UserRole 테이블에는 각 유저마다 어느 역할을 가지고 있는지 나타내는 데이터가 있다.
Tom은 POST_MANAGER와 SHOP_MANAGER 를, 나머지 두 명은 차례대로 HR_MANAGER와 SHOP_MANAGER 역할을 가지고 있다.

INSERT INTO UserRole (userId, role)
	values (1, 'POST_MANAGER'), (1, 'SHOP_MANAGER'),
		(2, 'HR_MANAGER'), (3, 'SHOP_MANAGER');
    

이제 각 유저별로 어떤 권한을 가지고 있는지 리스트로 뽑아볼 것이다.
간단하게 생각하면 아래와 같은 쿼리로 데이터를 추출할 수 있다고 생각이 들 것이다.

select * from user u left join UserRole ur on u.id = ur.userId;

하지만 Tom과 연결된 role이 두 개 이상이기 때문에 User 테이블에 UserRole 테이블이 left join 되면서 Tom의 정보가 중복되서 나오는 문제가 생긴다.

id    username userId	 role
1	Tom	1	POST_MANAGER
1	Tom	1	SHOP_MANAGER
2	Jerry	2	HR_MANAGER
3	Happy	3	SHOP_MANAGER

각 유저별로 어떠한 권한들을 가지고 있는지 가져오기 위해서 아래와 같이 쿼리를 작성한다.

select 
	userId,
	MAX(CASE WHEN role = 'POST_MANAGER' THEN 1 END) as is_post_manager,
	MAX(CASE WHEN role = 'SHOP_MANAGER' THEN 1 END) as is_shop_manager,
	MAX(CASE WHEN role = 'HR_MANAGER' THEN 1 END) as is_hr_manager
	from UserRole
    group by userId;

위 쿼리의 결과를 살펴보면, 각 유저마다 PostManager인지, ShopManager인지, HRManager인지 알 수 있게 된다.

이 쿼리를 서브쿼리로 이용해서 User 테이블에 다시 조인하여 결과를 가져온다.

select u.*, urs.is_post_manager, urs.is_shop_manager, urs.is_hr_manager
	from user u left join (
		select 
        		userId,
		        MAX(CASE WHEN role = 'POST_MANAGER' THEN 1 END) as is_post_manager,
			MAX(CASE WHEN role = 'SHOP_MANAGER' THEN 1 END) as is_shop_manager,
			MAX(CASE WHEN role = 'HR_MANAGER' THEN 1 END) as is_hr_manager
		from UserRole
		group by userId
    ) urs on u.id = urs.userId;

쿼리 결과는 아래와 같다.

id, username, is_post_manager, is_shop_manager, is_hr_manager
1	Tom		1		1	
2	Jerry						1
3	Happy				1