오라클 세로를 가로로 pivot - olakeul seloleul galolo pivot

금백조의 개발 블로그

서론

프로젝트에서 개발을 진행하면서도 세로로 된 데이터를 가로로 변환하여 표현해야 할 경우가 많았습니다. 이번 기회에 세로를 가로로 변환하는 방법을 예제를 통하여 정리해보겠습니다.

본론

만약 아래와 같이 품목그룹 별 품목에 대한 수량 데이터가 있다고 가정해보겠습니다.

요구사항이 품목그룹별로 각 품목의 수량들을 가로로 표현해달라고 할 경우 PIVOT을 사용하여 표현하는 예제입니다.

위의 데이터는 #TEMP_TABLE 임시 테이블에 생성했습니다.

1. [세로를 가로로 변환 예제(정적)]

IF OBJECT_ID('tempdb..#TEMP_TABLE')--임시 테이블 남아있을시 삭제 IS NOT NULL DROP TABLE #TEMP_TABLE CREATE TABLE #TEMP_TABLE( ITEM_GROUP NVARCHAR(10)--품목그룹 ,ITEM NVARCHAR(10)--품목 ,QTY NUMERIC(10,0)--수량 ) INSERT INTO #TEMP_TABLE--데이터 생성 SELECT '품목그룹1' AS ITEM_GROUP, '품목1' AS ITEM ,100 AS QTY UNION ALL SELECT '품목그룹1', '품목2', 100 UNION ALL SELECT '품목그룹2', '품목3', 200 UNION ALL SELECT '품목그룹3', '품목4', 300 UNION ALL SELECT '품목그룹3', '품목5', 400 UNION ALL SELECT '품목그룹1', '품목5', 500 --PIVOT을 이용하여 세로데이터를 가로로 변환 SELECT ITEM_GROUP, [품목1],[품목2],[품목3],[품목4],[품목5] FROM ( SELECT ITEM_GROUP, ITEM, QTY FROM #TEMP_TABLE RESULT ) RESULT--세로를 가로로 변환시킬 Inline View PIVOT ( SUM(QTY) FOR ITEM IN ([품목1],[품목2],[품목3],[품목4],[품목5]) ) AS PIVOT_RESULT

[조회 결과]

하지만 위의 방법으로는 이후에 품목6, 품목7.. 에 대한 데이터가 추가될 경우 추가된 만큼 쿼리를 수정해야 됩니다.  따라서 가로로 변환될 데이터가 정적이지 않을 경우 동적 쿼리를 사용하여 변환해야 합니다.

가로 데이터를 추출하는 방법은 이전에 작성한 [[MSSQL]행 데이터를 문자열로 합치기]에서 사용했던 방법을 이용해보겠습니다.

2021/02/18 - [Database/SQL Server(MSSQL)] - [MSSQL]행 데이터를 문자열로 합치기

[MSSQL]행 데이터를 문자열로 합치기

서론 개발 요구사항 중 테이블에 존재하는 행 문자열 데이터들을 가로로 합쳐서 하나의 문자열로 표현해야 하는 요구사항이 있었습니다. 이 요구사항을 통해 다시 한번 정리하게 된 행 데이터

goldswan.tistory.com

2. [세로를 가로로 변환 예제(동적)]

IF OBJECT_ID('tempdb..#TEMP_TABLE')--임시 테이블 남아있을시 삭제 IS NOT NULL DROP TABLE #TEMP_TABLE CREATE TABLE #TEMP_TABLE( ITEM_GROUP NVARCHAR(10)--품목그룹 ,ITEM NVARCHAR(10)--품목 ,QTY NUMERIC(10,0)--수량 ) DECLARE @DYNAMIC_COLUMNS NVARCHAR(MAX) = '' DECLARE @SQL NVARCHAR(MAX) = '' INSERT INTO #TEMP_TABLE--데이터 생성 SELECT '품목그룹1' AS ITEM_GROUP, '품목1' AS ITEM ,100 AS QTY UNION ALL SELECT '품목그룹1', '품목2', 100 UNION ALL SELECT '품목그룹2', '품목3', 200 UNION ALL SELECT '품목그룹3', '품목4', 300 UNION ALL SELECT '품목그룹3', '품목5', 400 UNION ALL SELECT '품목그룹1', '품목5', 500 UNION ALL SELECT '품목그룹1', '품목6', 1000--품목6 추가 UNION ALL SELECT '품목그룹2', '품목7', 2000--품목7 추가 --가로로 변환할 데이터 중복을 제거하여 추출 SELECT @DYNAMIC_COLUMNS = CONCAT(@DYNAMIC_COLUMNS,'[', ITEM ,']',',') FROM ( SELECT DISTINCT ITEM FROM #TEMP_TABLE ) A --실제 조회되는 결과 [품목1],[품목2],[품목3],[품목4],[품목5],[품목6],[품목7], --마지막 , 제거 처리 SET @DYNAMIC_COLUMNS = CASE WHEN LEN(@DYNAMIC_COLUMNS)=0 THEN '' ELSE LEFT(@DYNAMIC_COLUMNS, LEN(@DYNAMIC_COLUMNS)-1) END --PIVOT을 이용하여 세로데이터를 가로로 변환하는 동적 쿼리 SET @SQL = ' SELECT ITEM_GROUP,'+@DYNAMIC_COLUMNS+' FROM ( SELECT ITEM_GROUP, ITEM, QTY FROM #TEMP_TABLE RESULT ) RESULT--세로를 가로로 변환시킬 Inline View PIVOT ( SUM(QTY) FOR ITEM IN ('+@DYNAMIC_COLUMNS+') ) AS PIVOT_RESULT' EXECUTE SP_EXECUTESQL @SQL

[조회 결과]

결론

오늘은 PIVOT을 이용하여 세로를 가로로 변환하는 방법을 정리해보았습니다. 자주 사용하는 PIVOT을 정리했으니 이후에도 참고하여 활용해야겠습니다!

오라클에서 PIVOT을 사용하다보면 IN절에 동적으로 값을 할당하고 싶을때가 있다. 그러나 PIVOT의 IN절은 SQL의 IN절과 다르다. PIVOT의 IN절은 할당된 값의 개수와 순서를 맞춰서 결과가 출력된다.

일반적인 쿼리문의 PIVOT은 IN절에 동적으로 값을 할당 할 수 없지만, PIVOT XML 또는 Dynamic SQL을 활용하여 동적 PIVOT을 구현 할 수 있다.

일반적인 PIVOT 쿼리

SELECT * FROM (SELECT job , deptno , sal FROM emp ) PIVOT ( SUM(sal) FOR deptno IN ('10', '20', '30', '40') ) ORDER BY job

PIVOT 쿼리는 IN절 ('10', '20', '30', '40')의 값과 순서에 따라서 결과가 출력된다. '40'이라는 값이 테이블에 존자하지 않아도 결과에 NULL로 출력이 된다.

PIVOT XML을 사용하는 방법

PIVOT XML (IN절에 서브쿼리를 사용가능)

SELECT * FROM (SELECT job , deptno , sal FROM emp ) PIVOT XML ( SUM(sal) FOR deptno IN (SELECT deptno FROM dept) ) ORDER BY job

PIVOT XML은 PIVOT와 다르게 IN절에 서브쿼리를 사용할 수 있다. PIVOT와 다르게 IN절 인자 값(deptno)의 오름차순으로 결과가 출력되며, emp테이블에 해당 값이 없으면 출력되지 않는다.

IN절의 서브쿼리에 파라미터를 사용하여 값을 동적으로 바꿀 수 있다. 

ex) deptno IN (SELECT deptno FROM dept WHERE INSTR('파라미터(10,20,30)', deptno) > 0)

PIVOT XML 사용시 결과가 XML로 리턴됨

<PivotSet> <item> <column name="DEPTNO">10</column> <column name="SUM(SAL)" /> </item> <item> <column name="DEPTNO">20</column> <column name="SUM(SAL)">6000</column> </item> <item> <column name="DEPTNO">30</column> <column name="SUM(SAL)" /> </item> </PivotSet>

EXTRACTVALUE 함수를 사용하여 XML 값을 순서대로 값을 추출

SELECT job , EXTRACTVALUE(deptno_xml, '/PivotSet/item[1]/column[2]') dept1 , EXTRACTVALUE(deptno_xml, '/PivotSet/item[2]/column[2]') dept2 , EXTRACTVALUE(deptno_xml, '/PivotSet/item[3]/column[2]') dept3 FROM (SELECT job , deptno , sal FROM emp ) PIVOT XML ( SUM(sal) FOR deptno IN (SELECT deptno FROM dept) ) ORDER BY job

PIVOT은 IN절에 할당된 인자 값이 컬럼명으로 출력이 되지만, PIVOT XML은 쿼리문에 작성한 별칭(dept1...)이 출력된다.

Dynamic SQL을 사용하는 방법

Dynamic SQL을 사용하는 Procedure를 생성하여 사용하는 방법

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 deptno FROM dept ORDER BY 1) LOOP sql_param := sql_param || '''' || x.deptno || '''' || ', '; END LOOP; sql_param := SUBSTR(sql_param, 1, LENGTH(sql_param)-2); sql_query := sql_query || ' SELECT * '; sql_query := sql_query || ' FROM (SELECT job '; sql_query := sql_query || ' , deptno '; sql_query := sql_query || ' , sal '; sql_query := sql_query || ' FROM emp '; sql_query := sql_query || ' ) '; sql_query := sql_query || ' PIVOT ( SUM(sal) '; sql_query := sql_query || ' FOR deptno IN (' || sql_param || ') '; sql_query := sql_query || ' ) '; sql_query := sql_query || 'ORDER BY job '; open p_cursor for sql_query; END;

Dynamic SQL을 사용하면 기존 PIVOT와 동일한 결과가 출력되지만 Procedure를 생성해야하는 번거로움이 있다.

사용 예제

PIVOT XML을 사용하는 방법

Dynamic SQL을 사용하는 방법

사용_예제_쿼리.txt

0.01MB