MSSQL PIVOT 여러컬럼 동적으로 수행하기

MSSQL PIVOT

이번 피드에서는 이전에 다루었던 피벗테이블의 응용인 여러컬럼을 피벗처리하는 방법에 대해 소개하도록 하겠다.

 

이전 피벗에 대한 내용을 보지 않고 왔다면 아래 링크를 따라 피벗하는 법을 확인 후 이번 피드를 보시길 추천한다.

 

2019/08/21 - [프로그래밍/MS-SQL] - MSSQL PIVOT 동적으로 수행하기

MSSQL PIVOT 동적으로 수행하기

 

여러컬럼 피봇수행

이전 피드에서는  '품목/매출월/매출액' 에 대해 피봇을 수행하였다면, 이번에는 '품목/매출월/매출액/매출수량' 을 피봇처리하는 과정을 설명할 것이다.

 

 

PIVOT 형태로 변경

1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT.. INTO하여 저장

2. PIVOT처리할 항목들을 나열 (행에서 열로 변경할 항목들 나열)

3. 문자열의 마지막 index에 있는 , 제거

4. 동적으로 PIVOT처리할 쿼리문 생성

5. PIVOT쿼리 수행

 

 

1. 임시테이블에 SELECT.. INTO하여 저장

	-- 1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT ... INTO 하여 저장한다.
	SELECT
		 PRD_NAME
		,YYYYMM
		,QTY
		,AMT
		,COL = YYYYMM + '_' + COL
		,VALUE
	INTO #TEMP_TABLE
	FROM
	(
		SELECT
			 PRD_NAME		-- 품목
			,YYYYMM			-- 년월
			,SUM(QTY)	QTY	-- 수량
			,SUM(AMT)	AMT	-- 금액
		FROM SALE
		GROUP BY PRD_NAME, YYYYMM
	) RES
	CROSS APPLY
	(
		SELECT '매출수량', QTY UNION ALL
		SELECT '매출액', AMT 
	) C (COL,VALUE);

임시테이블에 아래와 같은 결과를 가진 테이블을 저장한다.

 

여기서 우리가 행데이터를 열로 변환해야할 항목은 [COL] 컬럼이다.

 

CROSS APPLY를 이용하여 매출액,매출수량별 값을 갖도록 테이블을 변환시켰다.

 

 

 

4. 동적으로 PIVOT처리할 쿼리문 생성

	-- 2. 피봇처리할 항목들을 나열한다. (행에서 열로 변경할 항목들 나열)
	DECLARE @PIVOT_COLS	VARCHAR(MAX)	-- 피봇대상 컬럼항목 생성
	DECLARE @EXEC_QUERY	VARCHAR(MAX)	-- 피봇대상 쿼리

	SET @PIVOT_COLS = ''
	SET @EXEC_QUERY =''

	SELECT @PIVOT_COLS = @PIVOT_COLS + '['+ COL +'],' 	  
	FROM (
			SELECT DISTINCT 
				COL
			FROM #TEMP_TABLE
		 ) AS A

	-- 3. 문자열 맨뒤에 ,항목 삭제 처리
	SET @PIVOT_COLS = LEFT(@PIVOT_COLS, LEN(@PIVOT_COLS) - 1)

	PRINT(@PIVOT_COLS)
	
	-- 4. 동적으로 피봇처리할 쿼리를 만든다.
	SET @EXEC_QUERY = 'SELECT 
						 Z.PRD_NAME		품목
						,'+@PIVOT_COLS+'
					   FROM
					   (
							SELECT 
								 PRD_NAME
								,COL
								,VALUE
							FROM #TEMP_TABLE
							) X
							PIVOT (SUM(VALUE) 
							FOR COL IN ('+ @PIVOT_COLS +')
					   ) AS Z'

이전 피드에서 설명한 내용은 생략하고 넘어가도록 한다. ( 2번,3번은 동일한 과정임)

@EXEC_QUERY : 동적으로 피봇처리하기 위해 스트링쿼리를 만든다.

 

피봇을 수행할 컬럼은 [COL]이고, 피봇된 결과에 대한 집계는 [VALUE]이다.

 

 

전체스크립트

/*------------------------------
	-- PIVOT 형태로 변경
	-- 1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT ... INTO 하여 저장한다.
	-- 2. 피봇처리할 항목들을 나열한다. (행에서 열로 변경할 항목들 나열)
	-- 3. 문자열 맨뒤에 ,항목 삭제 처리
	-- 4. 동적으로 피봇처리할 쿼리를 만든다.
	-- 5. 피봇쿼리 수행
	------------------------------*/

	-- 1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT ... INTO 하여 저장한다.
	SELECT
		 PRD_NAME
		,YYYYMM
		,QTY
		,AMT
		,COL = YYYYMM + '_' + COL
		,VALUE
	--INTO #TEMP_TABLE
	FROM
	(
		SELECT
			 PRD_NAME		-- 품목
			,YYYYMM			-- 년월
			,SUM(QTY)	QTY	-- 수량
			,SUM(AMT)	AMT	-- 금액
		FROM SALE
		GROUP BY PRD_NAME, YYYYMM
	) RES
	CROSS APPLY
	(
		SELECT '매출수량', QTY UNION ALL
		SELECT '매출액', AMT 
	) C (COL,VALUE);


	-- 2. 피봇처리할 항목들을 나열한다. (행에서 열로 변경할 항목들 나열)
	DECLARE @PIVOT_COLS	VARCHAR(MAX)	-- 피봇대상 컬럼항목 생성
	DECLARE @EXEC_QUERY	VARCHAR(MAX)	-- 피봇대상 쿼리

	SET @PIVOT_COLS = ''
	SET @EXEC_QUERY =''

	SELECT @PIVOT_COLS = @PIVOT_COLS + '['+ COL +'],' 	  
	FROM (
			SELECT DISTINCT 
				COL
			FROM #TEMP_TABLE
		 ) AS A

	-- 3. 문자열 맨뒤에 ,항목 삭제 처리
	SET @PIVOT_COLS = LEFT(@PIVOT_COLS, LEN(@PIVOT_COLS) - 1)

	PRINT(@PIVOT_COLS)
	
	-- 4. 동적으로 피봇처리할 쿼리를 만든다.
	SET @EXEC_QUERY = 'SELECT 
						 Z.PRD_NAME		품목
						,'+@PIVOT_COLS+'
					   FROM
					   (
							SELECT 
								 PRD_NAME
								,COL
								,VALUE
							FROM #TEMP_TABLE
							) X
							PIVOT (SUM(VALUE) 
							FOR COL IN ('+ @PIVOT_COLS +')
					   ) AS Z'
					  

	-- 5. 피봇쿼리 수행	
	EXEC (@EXEC_QUERY)
	PRINT @EXEC_QUERY
	DROP TABLE #TEMP_TABLE

 

 

 

제 블로그의 내용이 도움이 되셨나요?

여러분의 공감과 댓글이 큰 힘이됩니다 :)

▼▼▼▼▼▼▼▼▼▼▼