[착한코딩_실무에서 유용한 SQL 쿼리_Pivot] 오라클 SQL 쿼리

-- SURVEY 테이블을 만든다
CREATE TABLE SURVEY (
    USER_NAME VARCHAR2(20),--사용자 이름
    QUESTION VARCHAR2(20),--질문 번호
    ANSWER1 VARCHAR2(20),--객관식 답변
    ANSWER2 VARCHAR2(20),--주관식 답변
    CONSTRAINT SURVEY_PK PRIMARY KEY (USER_NAME, QUESTION)
);
-- SURVEY 테이블을 지운다
-- DROP TABLE SURVEY;    
-- SURVEY 테이블의 데이터 지운다
-- DELETE TABLE SURVEY;    

-- 테이블이 정상적으로 생성된 것을 확인
SELECT * FROM SURVEY; 

-- 홍길동, 강감찬, 이순신 3명의 고객이
-- 5개의 질문에 답변한 데이터를 INSERT 한다
-- 1,2,3,5는 객관식, 4는 주관식
INSERT INTO SURVEY VALUES('홍길동', 1, 3, NULL);
INSERT INTO SURVEY VALUES('홍길동', 2, 5, NULL);
INSERT INTO SURVEY VALUES('홍길동', 3, 2, NULL);
INSERT INTO SURVEY VALUES('홍길동', 4, NULL, '허균');
INSERT INTO SURVEY VALUES('홍길동', 5, 1, NULL);
    INSERT INTO SURVEY VALUES('강감찬', 1, 3, NULL);
    INSERT INTO SURVEY VALUES('강감찬', 2, 1, NULL);
    INSERT INTO SURVEY VALUES('강감찬', 3, 2, NULL);
    INSERT INTO SURVEY VALUES('강감찬', 4, NULL, '귀주대첩');
    INSERT INTO SURVEY VALUES('강감찬', 5, 2, NULL);
        INSERT INTO SURVEY VALUES('이순신', 1, 4, NULL);
        INSERT INTO SURVEY VALUES('이순신', 2, 1, NULL);
        INSERT INTO SURVEY VALUES('이순신', 3, 4, NULL);
        INSERT INTO SURVEY VALUES('이순신', 4, NULL, '명량해전');
        INSERT INTO SURVEY VALUES('이순신', 5, 5, NULL);

-- INSERT된 데이터를 확인한다.
SELECT * FROM SURVEY;    

-- PIVOT 함수를 사용해 행을 열로 변환한다.
SELECT * FROM SURVEY
    PIVOT(MAX(ANSWER1) FOR QUESTION IN (1,2,3,4,5)
);

--주관식일 경우, ANSWER1컬럼을 주관식 답으로 대체한다.
WITH TEMP_TABLE AS
(
    SELECT 
    USER_NAME,
    QUESTION,
    CASE WHEN ANSWER2 IS NOT NULL THEN ANSWER2
         ELSE ANSWER1
    END ANSWER1
    FROM SURVEY
)
SELECT * FROM TEMP_TABLE
PIVOT(MAX(ANSWER1) FOR QUESTION IN (1,2,3,4,5));

착한코딩_실무에서 유용한 SQL 쿼리_Pivot.txt

0.00MB

 

 

[QUESTION IN 절 뒤에 상수가 아닌 조회된 값을 사용하는 방법]

XML문법으로 서브쿼리를 사용하는 방법과 다이나믹 SQL문법을 사용하는 방법이 있는데요.

아래 블로그의 다이나믹 SQL문을 참고해서, 위에 예제에 맞게 수정했습니다.

CREATE OR REPLACE PROCEDURE pc_dynamic_pivot(p_cursor in out sys_refcursor)
AS
    sql_param VARCHAR2(1000);
    sql_query VARCHAR2(4000);
BEGIN
    FOR x IN (SELECT DISTINCT QUESTION FROM SURVEY)
    LOOP
       sql_param := sql_param || '''' || x.QUESTION || '''' || ', ';
    END LOOP;
    
    sql_param := SUBSTR(sql_param, 1, LENGTH(sql_param)-2);

    sql_query := sql_query || ' SELECT *                           ';
    sql_query := sql_query || ' FROM SURVEY                        ';
    sql_query := sql_query || ' PIVOT( MAX(ANSWER1) FOR QUESTION   '; 
    sql_query := sql_query || '        IN (' || sql_param || ')    ';
    sql_query := sql_query || '      )                             ';
    open p_cursor for sql_query;
END; 

var rc refcursor
exec pc_dynamic_pivot(:rc)
print rc;

착한코딩_실무에서 유용한 SQL 쿼리_Pivot _다이나믹_SQL.txt

0.00MB

** 참고 블로그 : https://gent.tistory.com/336