[MSSQL]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]행 데이터를 문자열로 합치기

 

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을 정리했으니 이후에도 참고하여 활용해야겠습니다!