ORACLE PIVOT 쿼리 - 가로세로변환, ROW 단위 자료를 COLUMN 단위 자료로 변환2(예제2)

2020.06.12 17:29

졸리운_곰 조회 수:1334

PIVOT 쿼리 - 가로세로변환, ROW 단위 자료를 COLUMN 단위 자료로 변환2(예제2)

먼저 SCOTT/TIGER 로 로그인 한 후 아래와 같은 TEST2 테이블을 만들고 데이타까지 넣는다.

CREATE TABLE TEST2

(

COL1 VARCHAR2(10 BYTE),

COL2 VARCHAR2(10 BYTE),

COL3 VARCHAR2(10 BYTE),

COL4 VARCHAR2(10 BYTE)

)

Insert into TEST2

(COL1, COL2, COL3, COL4)

Values

('A', 'B', 'C', 'D');

Insert into TEST2

(COL1, COL2, COL3, COL4)

Values

('B', NULL, NULL, NULL);

Insert into TEST2

(COL1, COL2, COL3, COL4)

Values

(NULL, NULL, 'D', NULL);

Insert into TEST2

(COL1, COL2, COL3, COL4)

Values

('C', NULL, NULL, NULL);

Insert into TEST2

(COL1, COL2, COL3, COL4)

Values

(NULL, 'C', NULL, 'E');

SELECT * FROM TEST2 해 보면 (자료형은 모두 varchar2(10))

중간 중간 NULL 값들이 많이 들어가 있음. 아래와 같은 모습임.

COL1  COL2  COL3  COL4
----  ----  ----  ----
a     b     c     d
b
            d
c
      c           e

위의 모양을 중간에 있는 널값들을 다 없애고 아래처럼 바꾸려고 한다.

COL1  COL2  COL3  COL4
----  ----  ----  ----
a     b     c     d
b     c     d     e
c

* 작업 순서

1. 중간에 이빨 빠진 것 같은 모양을 위에서 부터 제대로 다 나오게 하려면 먼저

COL1 COL2 COL3 COL4 의 4개의 컬럼들을 union all 로 하나의 컬럼으로 합쳐야 한다.

2. 합칠 때 col1등 에 있던 데이타임을 기록하기 위해 grp_no 식으로 컬럼 별로 인식번호를 부여하고 같은 컬럼번호에서도 각 데이타마다 rnum 식으로 일련번호를 부여해야 한다.

3. rnum 이 같은 것들을 대상으로 max(decode(...)) 함수 실행시키면 된다(rnum 이 같은 것들끼리 group by 해야 한다는 말)

*********************************************************

차례대로 해 보면 다음과 같다.

먼저 아래의 쿼리로 4개의 필드들을 모두 하나의 필드로 합친다.

SELECT 1 GRP

, COL1 COL

, DECODE(COL1,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL1)) RNUM

FROM TEST2

UNION ALL

SELECT 2

, COL2

, DECODE(COL2,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL2))

FROM TEST2

UNION ALL

SELECT 3

, COL3

, DECODE(COL3,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL3))

FROM TEST2

UNION ALL

SELECT 4

, COL4

, DECODE(COL4,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL4))

FROM TEST2

위의 쿼리를 실행하면 아래와 같이 나온다. 아래 부분은 캡처된 화면이 좀 잘린 것임.

이제 원래 하던대로 grp 와 rnum max(decode( ...)) 를 사용하여 가로로 만든다.

일단 SELECT 할 때, DECODE 를 사용하면서 COL4 까지 컬럼을 총 4개를 만드는 쿼리를 아래처럼 만들어 돌린다.

SELECT DECODE (GRP, 1, COL) COL1

, DECODE (GRP, 2, COL) COL2

, DECODE (GRP, 3, COL) COL3

, DECODE (GRP, 4, COL) COL4

, RNUM

FROM (

SELECT 1 GRP

, COL1 COL

, DECODE(COL1,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL1)) RNUM

FROM TEST2

UNION ALL

SELECT 2

, COL2

, DECODE(COL2,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL2))

FROM TEST2

UNION ALL

SELECT 3

, COL3

, DECODE(COL3,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL3))

FROM TEST2

UNION ALL

SELECT 4

, COL4

, DECODE(COL4,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL4))

FROM TEST2

)

위의 쿼리를 실행시키면 아래와 같은 모양이 나온다.

여기서 RNUM 이 같은 것끼리 묶어서 MAX 값 가져와 주면 된다.

혹시 모르니 확인 차 RNUM 이 1 인것만 뽑아보자. 아래 쿼리다.

SELECT DECODE (GRP, 1, COL) COL1

, DECODE (GRP, 2, COL) COL2

, DECODE (GRP, 3, COL) COL3

, DECODE (GRP, 4, COL) COL4

, RNUM

FROM (

SELECT 1 GRP

, COL1 COL

, DECODE(COL1,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL1)) RNUM

FROM TEST2

UNION ALL

SELECT 2

, COL2

, DECODE(COL2,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL2))

FROM TEST2

UNION ALL

SELECT 3

, COL3

, DECODE(COL3,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL3))

FROM TEST2

UNION ALL

SELECT 4

, COL4

, DECODE(COL4,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL4))

FROM TEST2

)

WHERE RNUM = 1

위 쿼리를 실행시키면 아래와 같은 모양이 된다.

딱 봐도 MAX 값을 취하면 제대로 한 줄로 나오게 보임. NULL 값은 함수나 연산에서 모두 제외되므로 MAX 든 MIN 이든 나머지는 다 통과하고 A,B,C,D 만 남게 될 것.

최종적인 쿼리는 아래와 같다.

SELECT MAX (DECODE (GRP, 1, COL)) COL1

, MAX (DECODE (GRP, 2, COL)) COL2

, MAX (DECODE (GRP, 3, COL)) COL3

, MAX (DECODE (GRP, 4, COL)) COL4

, RNUM

FROM (

SELECT 1 GRP

, COL1 COL

, DECODE(COL1,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL1)) RNUM

FROM TEST2

UNION ALL

SELECT 2

, COL2

, DECODE(COL2,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL2))

FROM TEST2

UNION ALL

SELECT 3

, COL3

, DECODE(COL3,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL3))

FROM TEST2

UNION ALL

SELECT 4

, COL4

, DECODE(COL4,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY COL4))

FROM TEST2

)

GROUP BY RNUM

HAVING RNUM IS NOT NULL

ORDER BY RNUM

위 쿼리를 실행시켜보면 아래와 같이 나온다. 처음에 의도하던대로 잘 나왔음.

SCOTT/TIGER 로 로그인하여 아래 스크립트 실행시켜서 TEST3 테이블 만든다.

CREATE TABLE TEST3

SELECT 345 aa

, 1 bb

, 3 cc

FROM DUAL

UNION ALL

SELECT 2

, 25

, 2

FROM DUAL

UNION ALL

SELECT 123

, 3

, 4

FROM DUAL

SELECT * FROM TEST3 해 보면 아래와 같다.

이걸 아래처럼 바꾸는게 최종 목표임.

SELECT AA FROM TEST3 하면 세로로 나오던 345, 2, 123 을

한 행에 가로로 나오게 하는 것임. BB , CC 필드도 AA 와 마찬가지로 가로로 데이타가 나오게 하는게 최종 목표.

AA BB CC

345     2    123
1     25     3
3      2     4

먼저 아래 쿼리로 3개의 필드를 하나의 필드로 합친다. 합칠 때 UNION ALL 을 쓰고, 각 필드별 고유 ID 부여(GRP)하고, 각각의 필드에 있는 데이타 들에도 고유번호를 부여한다(RNUM). 최종적으로 이 RNUM 으로 GROUP BY 하게 될 것.

SELECT 1 GRP,

AA COL1,

ROW_NUMBER() OVER (ORDER BY 1) RNUM

FROM TEST3

UNION ALL

SELECT 2 ,

BB,

ROW_NUMBER() OVER (ORDER BY 1)

FROM TEST3

UNION ALL

SELECT 3,

CC,

ROW_NUMBER() OVER (ORDER BY 1)

FROM TEST3

위 쿼리를 실행하면 아래와 같은 모습임.

이제 DECODE 문을 사용하여 아래 모양으로 만들어야 한다.

c1	c2	c3	일련번호
1			1
	2		2
		3	3
4			1
	5		2
		6	3
7			1
	8		2
		9	3

다음의 쿼리를 실행하면 된다.

SELECT DECODE(RNUM,1,COL1),

DECODE(RNUM,2,COL1),

DECODE(RNUM,3,COL1),

RNUM

FROM

(

SELECT 1 GRP,

AA COL1,

ROW_NUMBER() OVER (ORDER BY 1) RNUM

FROM TEST3

UNION ALL

SELECT 2 ,

BB,

ROW_NUMBER() OVER (ORDER BY 1)

FROM TEST3

UNION ALL

SELECT 3,

CC,

ROW_NUMBER() OVER (ORDER BY 1)

FROM TEST3

)

결과는 아래와 같다.

이제 GRP 로 GROUP BY 만 하면 최종적으로 완성된다.

SELECT MAX(DECODE(RNUM,1,COL1)) AA,

MAX(DECODE(RNUM,2,COL1)) BB,

MAX(DECODE(RNUM,3,COL1)) CC

FROM

(

SELECT 1 GRP,

AA COL1,

ROW_NUMBER() OVER (ORDER BY 1) RNUM

FROM TEST3

UNION ALL

SELECT 2 ,

BB,

ROW_NUMBER() OVER (ORDER BY 1)

FROM TEST3

UNION ALL

SELECT 3,

CC,

ROW_NUMBER() OVER (ORDER BY 1)

FROM TEST3

)

GROUP BY GRP

위 쿼리를 실행한 결과는 아래와 같다. 처음 의도한대로 잘 나왔음.

아무 계정으로나 로그인한다. WITH 구문 사용 VIEW 처럼 사용할 것이니 테이블 필요 없음.

다음 A ,B 테이블은 다음과 같다.

A 테이블

COL1 COL2

----- ------

가군 A-code

가군 B-code

나군 c-code

나군 d-code

나군 e-code

다군 f-code

다군 g-code

B 테이블

COL1 COL2

------ -------

가군 01세대

가군 02세대

가군 03세대

가군 04세대

나군 05세대

나군 06세대

나군 07세대

나군 08세대

다군 09세대

위 두개의 테이블을 이용하여 아래처럼 만들어 본다.

COL1 COL2 COL3

----- ------- -------

가군 A-code 01세대

B-code 02세대

03세대

04세대

나군 c-code 05세대

d-code 06세대

e-code 07세대

08세대

다군 f-code 09세대

아래처럼 WITH 구문을 이용하여 인라인뷰 부터 만든다.

WITH A AS (

SELECT '가군' COL1, 'A-code' COL2 FROM DUAL

UNION ALL

SELECT '가군', 'B-code' FROM DUAL

UNION ALL

SELECT '나군', 'c-code' FROM DUAL

UNION ALL

SELECT '나군', 'd-code' FROM DUAL

UNION ALL

SELECT '나군', 'e-code' FROM DUAL

UNION ALL

SELECT '다군', 'f-code' FROM DUAL

UNION ALL

SELECT '다군', 'g-code' FROM DUAL

B AS (

SELECT '가군' COL1, '01세대' COL2 FROM DUAL

UNION ALL

SELECT '가군' , '02세대' FROM DUAL

UNION ALL

SELECT '가군' , '03세대' FROM DUAL

UNION ALL

SELECT '가군' , '04세대' FROM DUAL

UNION ALL

SELECT '나군' , '05세대' FROM DUAL

UNION ALL

SELECT '나군' , '06세대' FROM DUAL

UNION ALL

SELECT '나군' , '07세대' FROM DUAL

UNION ALL

SELECT '나군' , '08세대' FROM DUAL

UNION ALL

SELECT '다군' , '09세대' FROM DUAL

)

우선 두 테이블을 아래 쿼리로 합쳐서 하나로 만든다(컬럼 두 개짜리 테이블처럼 됨)

WITH A AS (

SELECT '가군' COL1, 'A-code' COL2 FROM DUAL

UNION ALL

SELECT '가군', 'B-code' FROM DUAL

UNION ALL

SELECT '나군', 'c-code' FROM DUAL

UNION ALL

SELECT '나군', 'd-code' FROM DUAL

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

UNION ALL

SELECT '나군', 'e-code' FROM DUAL

UNION ALL

SELECT '다군', 'f-code' FROM DUAL

UNION ALL

SELECT '다군', 'g-code' FROM DUAL

B AS (

SELECT '가군' COL1, '01세대' COL2 FROM DUAL

UNION ALL

SELECT '가군' , '02세대' FROM DUAL

UNION ALL

SELECT '가군' , '03세대' FROM DUAL

UNION ALL

SELECT '가군' , '04세대' FROM DUAL

UNION ALL

SELECT '나군' , '05세대' FROM DUAL

UNION ALL

SELECT '나군' , '06세대' FROM DUAL

UNION ALL

SELECT '나군' , '07세대' FROM DUAL

UNION ALL

SELECT '나군' , '08세대' FROM DUAL

UNION ALL

SELECT '다군' , '09세대' FROM DUAL

)

SELECT 1GRP,

COL1,

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY 1) A_RNUM_COL1,

COL2,

ROW_NUMBER() OVER(PARTITION BY COL2 ORDER BY 1) A_RNUM_COL2

FROM A

UNION ALL

SELECT 2GRP,

COL1,

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY 1) B_RNUM_COL1,

COL2,

ROW_NUMBER() OVER(PARTITION BY COL2 ORDER BY 1) B_RNUM_COL2

FROM B

위 쿼리 실행시키면 아래와 같이 됨(아래쪽은 많이 잘린 그림임)

이런건 일단 GRP 나 A_RNUM_COL1 로 GROUP BY 해야 함. 그 후 DECODE 문 쓰면 됨.

어떻게 처리하는게 좋을지 확인차, 아래 쿼리처럼 A_RNUM_COL1 = 1 일때 데이타를 보자.

아래를 실행한다.

WITH A AS (

SELECT '가군' COL1, 'A-code' COL2 FROM DUAL

UNION ALL

SELECT '가군', 'B-code' FROM DUAL

UNION ALL

SELECT '나군', 'c-code' FROM DUAL

UNION ALL

SELECT '나군', 'd-code' FROM DUAL

UNION ALL

SELECT '나군', 'e-code' FROM DUAL

UNION ALL

SELECT '다군', 'f-code' FROM DUAL

UNION ALL

SELECT '다군', 'g-code' FROM DUAL

B AS (

SELECT '가군' COL1, '01세대' COL2 FROM DUAL

UNION ALL

SELECT '가군' , '02세대' FROM DUAL

UNION ALL

SELECT '가군' , '03세대' FROM DUAL

UNION ALL

SELECT '가군' , '04세대' FROM DUAL

UNION ALL

SELECT '나군' , '05세대' FROM DUAL

UNION ALL

SELECT '나군' , '06세대' FROM DUAL

UNION ALL

SELECT '나군' , '07세대' FROM DUAL

UNION ALL

SELECT '나군' , '08세대' FROM DUAL

UNION ALL

SELECT '다군' , '09세대' FROM DUAL

)

SELECT COL1,

DECODE(GRP,1, COL2) COL2,

DECODE(GRP,2,COL2) COL3,

A_RNUM_COL1

FROM

(

SELECT 1GRP,

COL1,

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY 1) A_RNUM_COL1,

COL2,

ROW_NUMBER() OVER(PARTITION BY COL2 ORDER BY 1) A_RNUM_COL2

FROM A

UNION ALL

SELECT 2GRP,

COL1,

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY 1) B_RNUM_COL1,

COL2,

ROW_NUMBER() OVER(PARTITION BY COL2 ORDER BY 1) B_RNUM_COL2

FROM B

)

WHERE A_RNUM_COL1 = 1

결과는 다음과 같다.

세 번째 컬럼 COL3 의 첫 행부터 01 세대, 그 다음 행에 05세대 등으로 나와야 하는데 전부 NULL 로 나온다.

이걸 위로 올리려면 MAX() 함수를 쓰는수 밖에 없는데 이걸 써서 기대한 모양이 나오려면 일단 표가 대각선 모양으로 나와야 한다.

표를 대각선 모양으로 나오게 하려면 GROUP BY 를 해야 하는데 위에서 보니 A_RNUM_COL1 의 값이 모두 1이다. 그렇다면 하나 더 GROUP BY 해야 한다. COL1 을 하나 더 넣으면 비슷한 모양이 나오지 않을까 싶다.

GROUP BY A_RNUM_COL1, COL1 을 추가해 보고 돌려보자.

아래 쿼리를 실행한다.

WITH A AS (

SELECT '가군' COL1, 'A-code' COL2 FROM DUAL

UNION ALL

SELECT '가군', 'B-code' FROM DUAL

UNION ALL

SELECT '나군', 'c-code' FROM DUAL

UNION ALL

SELECT '나군', 'd-code' FROM DUAL

UNION ALL

SELECT '나군', 'e-code' FROM DUAL

UNION ALL

SELECT '다군', 'f-code' FROM DUAL

UNION ALL

SELECT '다군', 'g-code' FROM DUAL

B AS (

SELECT '가군' COL1, '01세대' COL2 FROM DUAL

UNION ALL

SELECT '가군' , '02세대' FROM DUAL

UNION ALL

SELECT '가군' , '03세대' FROM DUAL

UNION ALL

SELECT '가군' , '04세대' FROM DUAL

UNION ALL

SELECT '나군' , '05세대' FROM DUAL

UNION ALL

SELECT '나군' , '06세대' FROM DUAL

UNION ALL

SELECT '나군' , '07세대' FROM DUAL

UNION ALL

SELECT '나군' , '08세대' FROM DUAL

UNION ALL

SELECT '다군' , '09세대' FROM DUAL

)

SELECT COL1,

DECODE(GRP,1, COL2) COL2,

DECODE(GRP,2,COL2) COL3,

A_RNUM_COL1

FROM

(

SELECT 1GRP,

COL1,

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY 1) A_RNUM_COL1,

COL2,

ROW_NUMBER() OVER(PARTITION BY COL2 ORDER BY 1) A_RNUM_COL2

FROM A

UNION ALL

SELECT 2GRP,

COL1,

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY 1) B_RNUM_COL1,

COL2,

ROW_NUMBER() OVER(PARTITION BY COL2 ORDER BY 1) B_RNUM_COL2

FROM B

)

GROUP BY A_RNUM_COL1, COL1 , GRP, COL2

ORDER BY COL1

실행시킨 결과는 아래와 같다.

대각선 모양이 나왔으므로 이제 DECODE (.. ) 앞에 MAX 함수만 써 주면 될 듯 하다.

아래를 실행한다.

WITH A AS (

SELECT '가군' COL1, 'A-code' COL2 FROM DUAL

UNION ALL

SELECT '가군', 'B-code' FROM DUAL

UNION ALL

SELECT '나군', 'c-code' FROM DUAL

UNION ALL

SELECT '나군', 'd-code' FROM DUAL

UNION ALL

SELECT '나군', 'e-code' FROM DUAL

UNION ALL

SELECT '다군', 'f-code' FROM DUAL

UNION ALL

SELECT '다군', 'g-code' FROM DUAL

B AS (

SELECT '가군' COL1, '01세대' COL2 FROM DUAL

UNION ALL

SELECT '가군' , '02세대' FROM DUAL

UNION ALL

SELECT '가군' , '03세대' FROM DUAL

UNION ALL

SELECT '가군' , '04세대' FROM DUAL

UNION ALL

SELECT '나군' , '05세대' FROM DUAL

UNION ALL

SELECT '나군' , '06세대' FROM DUAL

UNION ALL

SELECT '나군' , '07세대' FROM DUAL

UNION ALL

SELECT '나군' , '08세대' FROM DUAL

UNION ALL

SELECT '다군' , '09세대' FROM DUAL

)

SELECT COL1,

MAX(DECODE(GRP,1, COL2)) COL2,

MAX(DECODE(GRP,2,COL2)) COL3,

A_RNUM_COL1

FROM

(

SELECT 1GRP,

COL1,

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY 1) A_RNUM_COL1,

COL2,

ROW_NUMBER() OVER(PARTITION BY COL2 ORDER BY 1) A_RNUM_COL2

FROM A

UNION ALL

SELECT 2GRP,

COL1,

ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY 1) B_RNUM_COL1,

COL2,

ROW_NUMBER() OVER(PARTITION BY COL2 ORDER BY 1) B_RNUM_COL2

FROM B

)

GROUP BY A_RNUM_COL1, COL1

ORDER BY COL1

위 쿼리 실행한 결과는 아래와 같다.

처음에 의도했던 대로 다 잘 나왔다.

아무 계정으로나 로그인한다. WITH 구문 사용 VIEW 처럼 사용할 것이니 계정구분할 필요없다.

아래와 같은 데이타들이 들어가 있다.

time name city

09:00 영화1 서울

09:00 영화1 대전

09:00 영화2 대전

10:00 영화2 서울

10:00 영화2 대전

10:00 영화2 대구

11:00 영화1 광주

11:00 영화1 부산

11:00 영화3 대전

이걸 아래와 같은 모양으로 바꿔 본다.

영화1 영화2 영화3

09:00 서울 대전

대전

10:00 서울

대전

대구

11:00 광주 대전

부산

* 일단 이번 건은 UNION ALL 로 합치고 자시고 할게 없음.

그냥 하나짜리 테이블에서 하는 작업. 당연히 UNION ALL 할 것 없고, 일단 그룹별로 넘버링부터 해 놓는다.

WITH 구문으로 원래 모습(그룹별로 넘버링 한 것 포함)을 만들면 아래와 같다.

WITH MOVIE AS

(

SELECT '09:00' TIME, '영화1' NAME, '서울' CITY FROM DUAL UNION ALL

SELECT '09:00' ,'영화1' ,'대전' FROM DUAL UNION ALL

SELECT '09:00' ,'영화2' ,'대전' FROM DUAL UNION ALL

SELECT '10:00' ,'영화2' ,'서울' FROM DUAL UNION ALL

SELECT '10:00' ,'영화2' ,'대전' FROM DUAL UNION ALL

SELECT '10:00' ,'영화2' ,'대구' FROM DUAL UNION ALL

SELECT '11:00' ,'영화1' ,'광주' FROM DUAL UNION ALL

SELECT '11:00' ,'영화1' ,'부산' FROM DUAL UNION ALL

SELECT '11:00' ,'영화3' ,'대전' FROM DUAL

)

SELECT TIME,

NAME,

CITY,

ROW_NUMBER() OVER(PARTITION BY TIME ORDER BY 1) RNUM_TIME,

ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY TIME) RNUM_NAME,

ROW_NUMBER() OVER(PARTITION BY CITY ORDER BY TIME) RNUM_CITY

FROM MOVIE

SELECT 한 모습은 아래와 같다.

이제 DECODE 문을 써서 대각선 모양을 만들어 보기 위한 쿼리를 아래와 같이 작성해 본다.

어떤 필드로 ORDER BY 해야 하는지 눈으로 확인해 보면서 하기 위함이다.

WITH MOVIE AS

(

SELECT '09:00' TIME, '영화1' NAME, '서울' CITY FROM DUAL UNION ALL

SELECT '09:00' ,'영화1' ,'대전' FROM DUAL UNION ALL

SELECT '09:00' ,'영화2' ,'대전' FROM DUAL UNION ALL

SELECT '10:00' ,'영화2' ,'서울' FROM DUAL UNION ALL

SELECT '10:00' ,'영화2' ,'대전' FROM DUAL UNION ALL

SELECT '10:00' ,'영화2' ,'대구' FROM DUAL UNION ALL

SELECT '11:00' ,'영화1' ,'광주' FROM DUAL UNION ALL

SELECT '11:00' ,'영화1' ,'부산' FROM DUAL UNION ALL

SELECT '11:00' ,'영화3' ,'대전' FROM DUAL

)

SELECT TIME ,

DECODE(NAME,'영화1',CITY) 영화1,

DECODE(NAME,'영화2',CITY) 영화2,

DECODE(NAME,'영화3',CITY) 영화3,

RNUM_TIME,

RNUM_NAME,

RNUM_CITY

FROM

(

SELECT TIME,

NAME,

CITY,

ROW_NUMBER() OVER(PARTITION BY TIME ORDER BY 1) RNUM_TIME,

ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY TIME) RNUM_NAME,

ROW_NUMBER() OVER(PARTITION BY CITY ORDER BY TIME) RNUM_CITY

FROM MOVIE

)

위 쿼리를 실행하면 아래와 같이 나온다.

그런데 위의 RESULTSET 을 보니 9시 대전이 중복되는게 있다. 사실은 영화1 이냐 2냐로 구분이 가능하지만 현재 영화1, 2는 필드명이 되었으니 쿼리안에서 구분할 방법을 마련해야 한다.

시간과 도시 이름을 하나로 묶어서

ROW_NUMBER() OVER(PARTITION BY TIME , NAME ORDER BY 1) RNUM_TIME_NAME 를 추가한다.

수정한 쿼리는 아래와 같다.

WITH MOVIE AS

(

SELECT '09:00' TIME, '영화1' NAME, '서울' CITY FROM DUAL UNION ALL

SELECT '09:00' ,'영화1' ,'대전' FROM DUAL UNION ALL

SELECT '09:00' ,'영화2' ,'대전' FROM DUAL UNION ALL

SELECT '10:00' ,'영화2' ,'서울' FROM DUAL UNION ALL

SELECT '10:00' ,'영화2' ,'대전' FROM DUAL UNION ALL

SELECT '10:00' ,'영화2' ,'대구' FROM DUAL UNION ALL

SELECT '11:00' ,'영화1' ,'광주' FROM DUAL UNION ALL

SELECT '11:00' ,'영화1' ,'부산' FROM DUAL UNION ALL

SELECT '11:00' ,'영화3' ,'대전' FROM DUAL

)

SELECT TIME,

DECODE(NAME,'영화1',CITY) 영화1,

DECODE(NAME,'영화2',CITY) 영화2,

DECODE(NAME,'영화3',CITY) 영화3,

RNUM_TIME,

RNUM_NAME,

RNUM_CITY,

RNUM_TIME_NAME

FROM

(

SELECT TIME,

NAME,

CITY,

ROW_NUMBER() OVER(PARTITION BY TIME ORDER BY 1) RNUM_TIME,

ROW_NUMBER() OVER(PARTITION BY TIME , NAME ORDER BY 1) RNUM_TIME_NAME,

ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY TIME) RNUM_NAME,

ROW_NUMBER() OVER(PARTITION BY CITY ORDER BY TIME) RNUM_CITY

FROM MOVIE

)

GROUP BY TIME ,CITY, NAME, RNUM_TIME, RNUM_CITY, RNUM_NAME ,RNUM_TIME_NAME

ORDER BY TIME

위의 쿼리를 다시 날려보면 RESULTSET 은 아래와 같다.

위 RESULTSET 에서 보면 '9시' 와 '대전' 은 이제 대각선 모양이 되었다.

영화2 필드의 첫 ROW 에 대전이 오도록 하기 위해 TIME 과 RNUM_TIME_NAME 으로 GROUP BY 하고 MAX 값을 가져오도록 아래와 같이 쿼리를 만들고 실행한다.

WITH MOVIE AS

(

SELECT '09:00' TIME, '영화1' NAME, '서울' CITY FROM DUAL UNION ALL

SELECT '09:00' ,'영화1' ,'대전' FROM DUAL UNION ALL

SELECT '09:00' ,'영화2' ,'대전' FROM DUAL UNION ALL

SELECT '10:00' ,'영화2' ,'서울' FROM DUAL UNION ALL

SELECT '10:00' ,'영화2' ,'대전' FROM DUAL UNION ALL

SELECT '10:00' ,'영화2' ,'대구' FROM DUAL UNION ALL

SELECT '11:00' ,'영화1' ,'광주' FROM DUAL UNION ALL

SELECT '11:00' ,'영화1' ,'부산' FROM DUAL UNION ALL

SELECT '11:00' ,'영화3' ,'대전' FROM DUAL

)

SELECT TIME,

MAX(영화1) 영화1,

MAX(영화2) 영화2,

MAX(영화3) 영화3,

--RNUM_TIME,

RNUM_TIME_NAME

FROM

(

SELECT TIME,

CITY,

DECODE(NAME,'영화1',CITY) 영화1,

DECODE(NAME,'영화2',CITY) 영화2,

DECODE(NAME,'영화3',CITY) 영화3,

RNUM_TIME,

RNUM_NAME,

RNUM_CITY,

RNUM_TIME_NAME

FROM

(

SELECT TIME,

NAME,

CITY,

ROW_NUMBER() OVER(PARTITION BY TIME ORDER BY 1) RNUM_TIME,

ROW_NUMBER() OVER(PARTITION BY TIME , NAME ORDER BY 1) RNUM_TIME_NAME,

ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY TIME) RNUM_NAME,

ROW_NUMBER() OVER(PARTITION BY CITY ORDER BY TIME) RNUM_CITY

FROM MOVIE

) --이제 바로아래 GROUP BY 는 이제 안 해도 됨.

GROUP BY TIME ,CITY, NAME, RNUM_TIME, RNUM_CITY, RNUM_NAME ,RNUM_TIME_NAME

ORDER BY TIME

)

GROUP BY TIME, RNUM_TIME_NAME

ORDER BY TIME

위 쿼리를 실행하면 RESULTSET 은 아래와 같다.

이제 최초에 바라던 모습이 완성되었다.

[출처] https://blog.naver.com/acatholic/90149105841

본 웹사이트는 광고를 포함하고 있습니다.
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.

번호	제목	글쓴이	날짜	조회 수
공지	오라클 기본 샘플 데이터베이스	졸리운_곰	2014.01.02	86307
공지	[SQL컨셉] 서적 "SQL컨셉"의 샘플 데이타 베이스 SAMPLE DATABASE of ORACLE	가을의 곰을...	2013.02.10	78758
공지	[G_SQL] Sample Database	가을의 곰을...	2012.05.20	95506

첫 페이지 1 끝 페이지

쓰기

태그

ORACLE PIVOT 쿼리 - 가로세로변환, ROW 단위 자료를 COLUMN 단위 자료로 변환2(예제2)

PIVOT 쿼리 - 가로세로변환, ROW 단위 자료를 COLUMN 단위 자료로 변환2(예제2)

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다. 어린이용이며, 설치가 필요없는 브라우저 게임입니다. https://s1004games.com

댓글 0

로그인

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com