{{htmlmetatags>
metatag-description=(언피벗 쿼리, Unpivot Query, 언피벗, Unpivot, 데이터베이스, DBMS, SQL, MySQL, MariaDB, MSSQL, Oracle, Tibero, PostgreSQL)
metatag-og:description=(언피벗 쿼리, Unpivot Query, 언피벗, Unpivot, 데이터베이스, DBMS, SQL, MySQL, MariaDB, MSSQL, Oracle, Tibero, PostgreSQL)
}}

====== Unpivot Query ======

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

===== 테이블 명세 =====
^  물리명  ^  논리명  ^  타입  ^  길이  ^
| 년 | year | 문자형 | 100 |
| 1월 일수 | jan_day_cnt | 숫자형 | |
| 2월 일수 | feb_day_cnt | 숫자형 | |
| 3월 일수 | mar_day_cnt | 숫자형 | |
| 4월 일수 | apr_day_cnt | 숫자형 | |
| 5월 일수 | may_day_cnt | 숫자형 | |
| 6월 일수 | jun_day_cnt | 숫자형 | |
| 7월 일수 | jul_day_cnt | 숫자형 | |
| 8월 일수 | aug_day_cnt | 숫자형 | |
| 9월 일수 | sep_day_cnt | 숫자형 | |
| 10월 일수 | oct_day_cnt | 숫자형 | |
| 11월 일수 | nov_day_cnt | 숫자형 | |
| 12월 일수 | dec_day_cnt | 숫자형 | |

===== SQL 스크립트 =====
<sxh sql; gutter: true; title: DDL;>
CREATE TABLE ex_unpivot (
	year VARCHAR(100) NOT NULL,
	jan_day_cnt INT NOT NULL,
	feb_day_cnt INT NOT NULL,
	mar_day_cnt INT NOT NULL,
	apr_day_cnt INT NOT NULL,
	may_day_cnt INT NOT NULL,
	jun_day_cnt INT NOT NULL,
	jul_day_cnt INT NOT NULL,
	aug_day_cnt INT NOT NULL,
	sep_day_cnt INT NOT NULL,
	oct_day_cnt INT NOT NULL,
	nov_day_cnt INT NOT NULL,
	dec_day_cnt INT NOT NULL
);
</sxh>

<sxh sql; gutter: true; title: DML;>
INSERT INTO ex_unpivot (
	year
	, jan_day_cnt, feb_day_cnt, mar_day_cnt
	, apr_day_cnt, may_day_cnt, jun_day_cnt
	, jul_day_cnt, aug_day_cnt, sep_day_cnt
	, oct_day_cnt, nov_day_cnt, dec_day_cnt
) VALUES (
	'2025'
	, 31, 28, 31
	, 30, 31, 30
	, 31, 31, 30
	, 31, 30, 31
);
</sxh>

===== MySQL/MariaDB =====
<sxh sql; gutter: true;>
/*
	MySQL 4.0 이상/MariaDB 5.1 이상
*/
SELECT
	year
	, 'jan' AS month
	, jan_day_cnt AS day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'feb'
	, feb_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'mar'
	, mar_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'apr'
	, apr_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'may'
	, may_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'jun'
	, jun_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'jul'
	, jul_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'aug'
	, aug_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'sep'
	, sep_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'oct'
	, oct_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'nov'
	, nov_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'dec'
	, dec_day_cnt
FROM
	ex_unpivot
;
</sxh>

===== MSSQL =====
<sxh sql; gutter: true;>
/*
	MSSQL 2000 이상
*/
SELECT
	year
	, 'jan' AS month
	, jan_day_cnt AS day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'feb'
	, feb_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'mar'
	, mar_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'apr'
	, apr_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'may'
	, may_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'jun'
	, jun_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'jul'
	, jul_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'aug'
	, aug_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'sep'
	, sep_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'oct'
	, oct_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'nov'
	, nov_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'dec'
	, dec_day_cnt
FROM
	ex_unpivot
;

/*
	MSSQL 2005 이상
*/
SELECT
	year
	, REPLACE(month, '_day_cnt', '') AS month
	, day_cnt
FROM (
	SELECT
		year
		, jan_day_cnt
		, feb_day_cnt
		, mar_day_cnt
		, apr_day_cnt
		, may_day_cnt
		, jun_day_cnt
		, jul_day_cnt
		, aug_day_cnt
		, sep_day_cnt
		, oct_day_cnt
		, nov_day_cnt
		, dec_day_cnt
	FROM
		ex_unpivot
) src
UNPIVOT (
	day_cnt FOR month IN (
		jan_day_cnt
		, feb_day_cnt
		, mar_day_cnt
		, apr_day_cnt
		, may_day_cnt
		, jun_day_cnt
		, jul_day_cnt
		, aug_day_cnt
		, sep_day_cnt
		, oct_day_cnt
		, nov_day_cnt
		, dec_day_cnt
	)
) unpvt
;
</sxh>

===== Oracle/Tibero =====
<sxh sql; gutter: true;>
/*
	Oracle 9i 이상/Tibero 4 이상
*/
SELECT
	year
	, 'jan' AS month
	, jan_day_cnt AS day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'feb'
	, feb_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'mar'
	, mar_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'apr'
	, apr_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'may'
	, may_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'jun'
	, jun_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'jul'
	, jul_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'aug'
	, aug_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'sep'
	, sep_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'oct'
	, oct_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'nov'
	, nov_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'dec'
	, dec_day_cnt
FROM
	ex_unpivot
;

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

===== PostgreSQL =====
<sxh sql; gutter: true;>
/*
	PostgreSQL 7.1 이상
*/
SELECT
	year
	, 'jan' AS month
	, jan_day_cnt AS day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'feb'
	, feb_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'mar'
	, mar_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'apr'
	, apr_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'may'
	, may_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'jun'
	, jun_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'jul'
	, jul_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'aug'
	, aug_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'sep'
	, sep_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'oct'
	, oct_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'nov'
	, nov_day_cnt
FROM
	ex_unpivot
UNION ALL
SELECT
	year
	, 'dec'
	, dec_day_cnt
FROM
	ex_unpivot
;
</sxh>

{{tag>"쿼리" "Query" "응용"}}