Unpivot Query

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

테이블 명세

물리명	논리명	타입	길이
년	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 스크립트

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
);

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
);

MySQL/MariaDB

/*
	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
;

MSSQL

/*
	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
;

Oracle/Tibero

/*
	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'
	)
)
;

PostgreSQL

/*
	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
;

쿼리 Query 응용