잡개발자 리토의 잡위키 잡개발자 리토의 잡위키

사용자 도구

사이트 도구

사이드바

기술문서:레퍼런스:쿼리:응용:pivot_query

문서의 이전 판입니다!

Pivot Query

최소 지원 버전은 약간의 차이가 있을 수 있습니다.
물리명 논리명 타입
년 year 문자형
월 month 문자형
일수 day_cnt 숫자형 

CREATE TABLE ex_pivot (
	year VARCHAR(100) NOT NULL,
	month VARCHAR(100) NOT NULL,
	day_cnt INT NOT NULL
);


INSERT INTO ex_pivot (year, month, day_cnt) VALUES ('2025', 'jan', 31);
INSERT INTO ex_pivot (year, month, day_cnt) VALUES ('2025', 'feb', 28);
INSERT INTO ex_pivot (year, month, day_cnt) VALUES ('2025', 'mar', 31);
INSERT INTO ex_pivot (year, month, day_cnt) VALUES ('2025', 'apr', 30);
INSERT INTO ex_pivot (year, month, day_cnt) VALUES ('2025', 'may', 31);
INSERT INTO ex_pivot (year, month, day_cnt) VALUES ('2025', 'jun', 30);
INSERT INTO ex_pivot (year, month, day_cnt) VALUES ('2025', 'jul', 31);
INSERT INTO ex_pivot (year, month, day_cnt) VALUES ('2025', 'aug', 31);
INSERT INTO ex_pivot (year, month, day_cnt) VALUES ('2025', 'sep', 30);
INSERT INTO ex_pivot (year, month, day_cnt) VALUES ('2025', 'oct', 31);
INSERT INTO ex_pivot (year, month, day_cnt) VALUES ('2025', 'nov', 30);
INSERT INTO ex_pivot (year, month, day_cnt) VALUES ('2025', 'dec', 31);

MySQL/MariaDB

-- MySQL 5.0.1 이상/MariaDB 5.1 이상
SELECT
	year
	, MAX(CASE WHEN month = 'jan' THEN day_cnt ELSE NULL END) AS jan_day_cnt
	, MAX(CASE WHEN month = 'feb' THEN day_cnt ELSE NULL END) AS feb_day_cnt
	, MAX(CASE WHEN month = 'mar' THEN day_cnt ELSE NULL END) AS mar_day_cnt
	, MAX(CASE WHEN month = 'apr' THEN day_cnt ELSE NULL END) AS apr_day_cnt
	, MAX(CASE WHEN month = 'may' THEN day_cnt ELSE NULL END) AS may_day_cnt
	, MAX(CASE WHEN month = 'jun' THEN day_cnt ELSE NULL END) AS jun_day_cnt
	, MAX(CASE WHEN month = 'jul' THEN day_cnt ELSE NULL END) AS jul_day_cnt
	, MAX(CASE WHEN month = 'aug' THEN day_cnt ELSE NULL END) AS aug_day_cnt
	, MAX(CASE WHEN month = 'sep' THEN day_cnt ELSE NULL END) AS sep_day_cnt
	, MAX(CASE WHEN month = 'oct' THEN day_cnt ELSE NULL END) AS oct_day_cnt
	, MAX(CASE WHEN month = 'nov' THEN day_cnt ELSE NULL END) AS nov_day_cnt
	, MAX(CASE WHEN month = 'dec' THEN day_cnt ELSE NULL END) AS dec_day_cnt
FROM
	ex_pivot
GROUP BY
	year
;

MSSQL

-- MSSQL 2000 이상
SELECT
	year
	, MAX(CASE WHEN month = 'jan' THEN day_cnt ELSE NULL END) AS jan_day_cnt
	, MAX(CASE WHEN month = 'feb' THEN day_cnt ELSE NULL END) AS feb_day_cnt
	, MAX(CASE WHEN month = 'mar' THEN day_cnt ELSE NULL END) AS mar_day_cnt
	, MAX(CASE WHEN month = 'apr' THEN day_cnt ELSE NULL END) AS apr_day_cnt
	, MAX(CASE WHEN month = 'may' THEN day_cnt ELSE NULL END) AS may_day_cnt
	, MAX(CASE WHEN month = 'jun' THEN day_cnt ELSE NULL END) AS jun_day_cnt
	, MAX(CASE WHEN month = 'jul' THEN day_cnt ELSE NULL END) AS jul_day_cnt
	, MAX(CASE WHEN month = 'aug' THEN day_cnt ELSE NULL END) AS aug_day_cnt
	, MAX(CASE WHEN month = 'sep' THEN day_cnt ELSE NULL END) AS sep_day_cnt
	, MAX(CASE WHEN month = 'oct' THEN day_cnt ELSE NULL END) AS oct_day_cnt
	, MAX(CASE WHEN month = 'nov' THEN day_cnt ELSE NULL END) AS nov_day_cnt
	, MAX(CASE WHEN month = 'dec' THEN day_cnt ELSE NULL END) AS dec_day_cnt
FROM
	ex_pivot
GROUP BY
	year
;

-- MSSQL 2005 이상
SELECT
	year
	, [jan] AS jan_day_cnt
	, [feb] AS feb_day_cnt
	, [mar] AS mar_day_cnt
	, [apr] AS apr_day_cnt
	, [may] AS may_day_cnt
	, [jun] AS jun_day_cnt
	, [jul] AS jul_day_cnt
	, [aug] AS aug_day_cnt
	, [sep] AS sep_day_cnt
	, [oct] AS oct_day_cnt
	, [nov] AS nov_day_cnt
	, [dec] AS dec_day_cnt
FROM
	(SELECT
		year
		, month
		, day_cnt
	FROM
		ex_pivot
	) src PIVOT (
		MAX(day_cnt)
		FOR month IN (
			[jan]
			, [feb]
			, [mar]
			, [apr]
			, [may]
			, [jun]
			, [jul]
			, [aug]
			, [sep]
			, [oct]
			, [nov]
			, [dec]
		)
	) pvt
;

Oracle/Tibero

-- Oracle 8.1.6 이상/Tibero 3 이상
SELECT
	year
	, MAX(CASE WHEN month = 'jan' THEN day_cnt ELSE NULL END) AS jan_day_cnt
	, MAX(CASE WHEN month = 'feb' THEN day_cnt ELSE NULL END) AS feb_day_cnt
	, MAX(CASE WHEN month = 'mar' THEN day_cnt ELSE NULL END) AS mar_day_cnt
	, MAX(CASE WHEN month = 'apr' THEN day_cnt ELSE NULL END) AS apr_day_cnt
	, MAX(CASE WHEN month = 'may' THEN day_cnt ELSE NULL END) AS may_day_cnt
	, MAX(CASE WHEN month = 'jun' THEN day_cnt ELSE NULL END) AS jun_day_cnt
	, MAX(CASE WHEN month = 'jul' THEN day_cnt ELSE NULL END) AS jul_day_cnt
	, MAX(CASE WHEN month = 'aug' THEN day_cnt ELSE NULL END) AS aug_day_cnt
	, MAX(CASE WHEN month = 'sep' THEN day_cnt ELSE NULL END) AS sep_day_cnt
	, MAX(CASE WHEN month = 'oct' THEN day_cnt ELSE NULL END) AS oct_day_cnt
	, MAX(CASE WHEN month = 'nov' THEN day_cnt ELSE NULL END) AS nov_day_cnt
	, MAX(CASE WHEN month = 'dec' THEN day_cnt ELSE NULL END) AS dec_day_cnt
FROM
	ex_pivot
GROUP BY
	year
;

-- Oracle 11g 이상//Tibero 6 이상
SELECT
	*
FROM
	(SELECT
		year
		, month
		, day_cnt
	FROM
		ex_pivot
	) PIVOT (
		MAX(day_cnt)
		FOR month IN (
			'jan' AS jan_day_cnt
			, 'feb' AS feb_day_cnt
			, 'mar' AS mar_day_cnt
			, 'apr' AS apr_day_cnt
			, 'may' AS may_day_cnt
			, 'jun' AS jun_day_cnt
			, 'jul' AS jul_day_cnt
			, 'aug' AS aug_day_cnt
			, 'sep' AS sep_day_cnt
			, 'oct' AS oct_day_cnt
			, 'nov' AS nov_day_cnt
			, 'dec' AS dec_day_cnt
		)
	)
;

PostgreSQL

-- PostgreSQL 7.1 이상
SELECT
	year
	, MAX(CASE WHEN month = 'jan' THEN day_cnt ELSE NULL END) AS jan_day_cnt
	, MAX(CASE WHEN month = 'feb' THEN day_cnt ELSE NULL END) AS feb_day_cnt
	, MAX(CASE WHEN month = 'mar' THEN day_cnt ELSE NULL END) AS mar_day_cnt
	, MAX(CASE WHEN month = 'apr' THEN day_cnt ELSE NULL END) AS apr_day_cnt
	, MAX(CASE WHEN month = 'may' THEN day_cnt ELSE NULL END) AS may_day_cnt
	, MAX(CASE WHEN month = 'jun' THEN day_cnt ELSE NULL END) AS jun_day_cnt
	, MAX(CASE WHEN month = 'jul' THEN day_cnt ELSE NULL END) AS jul_day_cnt
	, MAX(CASE WHEN month = 'aug' THEN day_cnt ELSE NULL END) AS aug_day_cnt
	, MAX(CASE WHEN month = 'sep' THEN day_cnt ELSE NULL END) AS sep_day_cnt
	, MAX(CASE WHEN month = 'oct' THEN day_cnt ELSE NULL END) AS oct_day_cnt
	, MAX(CASE WHEN month = 'nov' THEN day_cnt ELSE NULL END) AS nov_day_cnt
	, MAX(CASE WHEN month = 'dec' THEN day_cnt ELSE NULL END) AS dec_day_cnt
FROM
	ex_pivot
GROUP BY
	year
;

문서 도구