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

사용자 도구

사이트 도구

사이드바

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

문서의 이전 판입니다!

Pivot Query

최소 지원 버전은 약간의 차이가 있을 수 있습니다.

테이블 명세

물리명 논리명 타입
년 year 문자형
월 month 문자형
일수 day_cnt 숫자형

SQL 스크립트

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
;

문서 도구