UNPIVOT - 가로데이터 세로로 출력하기

1. 테스트 테이블 생성

CREATE TABLE TEST(ACCOUNT1 NUMBER,ACCOUNT2 NUMBER,ACCOUNT3 NUMBER,ACCOUNT4 NUMBER,ACCOUNT5 NUMBER);

INSERT INTO TEST
VALUES(1,2,3,4,5);
INSERT INTO TEST
VALUES(6,7,8,9,10);
INSERT INTO TEST
VALUES(11,12,13,14,15);

 

2. 데이터 확인

SELECT ACCOUNT1, ACCOUNT2, ACCOUNT3, ACCOUNT4, ACCOUNT5
FROM TEST;

 

-데이터 확인-

 

3. UNION을 이용한 세로데이터

SELECT CODE AS ACCOUNT
FROM (
SELECT ACCOUNT1 AS CODE
FROM TEST
UNION
SELECT ACCOUNT2 AS CODE
FROM TEST
UNION
SELECT ACCOUNT3 AS CODE
FROM TEST
UNION
SELECT ACCOUNT4 AS CODE
FROM TEST  
UNION
SELECT ACCOUNT5 AS CODE
FROM TEST 
)

 

-UNION을 이용한 세로데이터-

 

4. UNPIVOT를 이용한 세로데이터

SELECT DISTINCT ACCOUNT, COL
FROM TEST
UNPIVOT(ACCOUNT FOR COL IN (ACCOUNT1, ACCOUNT2, ACCOUNT3, ACCOUNT4, ACCOUNT5))
ORDER BY ACCOUNT

 

-UNPIVOT를 이용한 세로데이터-