사이드바
기술문서:레퍼런스:쿼리:응용:unpivot_query
문서의 이전 판입니다!
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 ;