[ MySQL ] 동적 쿼리로 피벗 구현

동적 쿼리(다이나믹 쿼리)가 다양하게 쓰이는 편이고 그 케이스들중 하나를 정리해둔다.

 

다이나믹 쿼리 관련해서는 이전에 대충 쿼리만 써뒀던 글이 있다.

[ MySQL 쿼리 기초 ] 다이나믹 쿼리

위의 글에서 사용된 예시는 다음의 왼쪽과 같이 생긴 데이터를 오른쪽과 같이 변경해서 저장하는 쿼리이다.

위의 링크 글의 쿼리가 활용된 케이스

 

파츠별 장착 아이템 수를 집계하기 위해 사용했던 쿼리인데 범용적으로 쓰일 케이스는 아닌 것 같다.

동적 쿼리에서 쓰이는 요소들이 많이 들어가서 나중에 참고할 때 찾기 쉽게 써뒀던 글이다.

---

 

이번에는 "쿼리로 피벗 구현하기"라는 쓰임세를 중점으로 정리한다.

다음의 왼쪽과 같이 저장된 데이터를 오른쪽과 같이 출력하는 쿼리이다.

이번 글에서 정리할 쿼리가 활용된 케이스

 

요즘 웬만한 시각화 툴에서는 왼쪽과 같은 데이터를 그대로 SELECT해오고 피벗 및 그룹지을 필드를 설정하면 시각화 할 때 피벗해주는기능을 지원한다.

이번 케이스는 굳이 쿼리로 피벗을 구현해야 하는 케이스에 참고하자.

 

출력 결과에 필드명이 될 기존 데이터 type 필드값들을 하나씩 동적 쿼리에 추가해줘야 해서 type 필드값만 포함된 임시 테이블을 활용한다.

TRUNCATE TABLE temp_col;

INSERT INTO temp_col
SELECT type 
FROM from_table
WHERE cdate BETWEEN '2024-11-13' AND '2024-11-14'
GROUP BY type
;

 

이렇게 만들어진 테이블에서 cur_Items를 정의해 동적 쿼리를 구성할 때 다음과 같이 활용한다.

DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_items varchar(100);

DECLARE cur_Items CURSOR FOR
SELECT col_name FROM temp_col ORDER BY col_name;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

SET @stmt = CONCAT('select cdate ');

OPEN cur_Items;
get_Items: LOOP
	FETCH cur_Items INTO v_items;
	IF v_finished = 1 THEN LEAVE get_Items; END IF;
	
	SET @stmt = CONCAT(@stmt, ' , SUM(CASE WHEN type = \'',v_items,'\' THEN value ELSE 0 END) ');
	SET @stmt = CONCAT(@stmt, ' AS \'',v_items,'\' ');
	
END LOOP get_Items;
CLOSE cur_Items;
	SET @stmt = CONCAT(@stmt, ' FROM from_table A
                                    WHERE value > 0 
                                    AND cdate BETWEEN \'2024-11-13\' AND \'2024-11-14\'
                                    GROUP BY cdate
                                    ORDER BY cdate;');
PREPARE stmt1 FROM @stmt; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1;