MSSQL PIVOT: 2개의 글

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

Posted by 고 고특파원
2019. 8. 28. 10:07 프로그래밍/MS-SQL
MSSQL PIVOT

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

 

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

 

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

 

MSSQL PIVOT 동적으로 수행하기

프로그램을 개발하다보면 행(세로) 데이터를 열(가로)로 나열시켜 보여야하는 경우가 있다. 이때 PIVOT을 사용하면 행데이터를 열로 나열시켜 보일 수 있다. 우리는 품목/매출월/매출액 형식으로 되어있는 테이..

maelife.tistory.com

 

여러컬럼 피봇수행

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

 

 

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

 

 

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

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

▼▼▼▼▼▼▼▼▼▼▼

 

이 댓글을 비밀 댓글로

MSSQL PIVOT 동적으로 수행하기

Posted by 고 고특파원
2019. 8. 21. 09:39 프로그래밍/MS-SQL

 

프로그램을 개발하다보면 행(세로) 데이터를 열(가로)로 나열시켜 보여야하는 경우가 있다.

 

이때 PIVOT을 사용하면 행데이터를 열로 나열시켜 보일 수 있다.

 

PIVOT(피벗) 예시

우리는 품목/매출월/매출액 형식으로 되어있는 테이블을 품목 / 201901 / 201902 / ... / 201904 의 형태로 PIVOT처리하여 테이블을 보이도록 할 것이다.

 

아래에 설명할 내용을 천천히 따라가면서 PIVOT을 어떻게 처리하는지 확인해 보도록 한다.

 

PIVOT하는 과정을 간단한 매출(SALE) 테이블을 통해 설명하도록 하겠고, 순서는 아래와 같다.

 

 

PIVOT 순서

1. 매출 테이블생성

2. 매출 데이터 삽입

3. PIVOT 형태로 변경

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

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

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

  3-4) 동적으로 PIVOT처리할 쿼리문 생성

  3-5) PIVOT 쿼리 수행

 

 

1. 매출(SALE) 테이블 생성

CREATE TABLE SALE
(
	
	 YYYYMM		VARCHAR(6)			-- 년월
	,PRD_NAME	VARCHAR(200)		-- 품목
	,QTY		NUMERIC(18,3)		-- 수량
	,AMT		MONEY				-- 금액
)

매출(SALE) 테이블을 생성하는 쿼리이다.

 

매출 테이블에는 품목, 매출월, 매출액 컬럼이 있다.

 

 

2. 매출(SALE) 데이터 삽입

-- 2. 매출 데이터 입력
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201901', 'A', 5, 5000)			-- 품목 A 19년 1월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201902', 'A', 10, 10000)		-- 품목 A 19년 2월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201903', 'A', 5, 10000)		-- 품목 A 19년 3월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201904', 'A', 10, 10000)		-- 품목 A 19년 4월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201905', 'A', 10, 10000)		-- 품목 A 19년 5월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201901', 'B', 20, 40000)		-- 품목 B 19년 1월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201902', 'B', 10, 20000)		-- 품목 B 19년 2월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201903', 'B', 5, 10000)		-- 품목 B 19년 3월 매출
INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201904', 'B', 10, 20000)		-- 품목 B 19년 4월 매출

매출(SALE) 테이블에 임의로 데이터를 생성하여 INSERT하고 있는 부분이다.

 

 

3. PIVOT 형태로 변경

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

-- 1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT ... INTO 하여 저장
SELECT
	 PRD_NAME		-- 품목
	,YYYYMM			-- 년월
	,SUM(AMT)	AMT	-- 금액
INTO #TEMP_TABLE
FROM SALE
GROUP BY PRD_NAME, YYYYMM

품목별 월별로 GROUP BY한 결과테이블을 임시테이블(#TEMP_TABLE)에 저장한다.

 

 

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

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

SET @PIVOT_COLS = ''
SET @TOTAL_SUM = ''
SET @EXEC_QUERY =''
SELECT @PIVOT_COLS = @PIVOT_COLS + '['+ YYYYMM +'],' 
	  ,@TOTAL_SUM = @TOTAL_SUM + 'ISNULL([' + YYYYMM + '],0) +'
FROM (
			SELECT DISTINCT 
				YYYYMM
			FROM #TEMP_TABLE
	 ) AS A

@PIVOT_COLS : 피봇을 수행하여 생성될 컬럼들을 나열하는 변수 

 

결과형태

[201901],[201902],[201903],[201904],[201905]

 

 

@TOTAL_SUM : 피봇을 수행하여 생성된 컬럼들의 총합계를 계산하기 위한 변수 

 

결과형태

ISNULL([201901],0) +ISNULL([201902],0) +ISNULL([201903],0) +ISNULL([201904],0) +ISNULL([201905],0)

 

 

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

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

[201901],[201902],[201903],[201904],[201905],

문자열이 이런식으로 만들어진것인데 맨 마지막의 [ , ] 를 제거해주는 과정이다.

 

 

3-4) 동적으로 PIVOT처리할 쿼리문 생성

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

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

결과형태

SELECT 
	 Z.PRD_NAME		품목
	,[201901],[201902],[201903],[201904],[201905]
	,ISNULL([201901],0) +ISNULL([201902],0) +ISNULL([201903],0) +ISNULL([201904],0) +ISNULL([201905],0) 	AS 총매출액
FROM
(
	SELECT 
		 PRD_NAME
		,YYYYMM
		,AMT
	FROM #TEMP_TABLE
) X
PIVOT (SUM(AMT) 
FOR YYYYMM IN ([201901],[201902],[201903],[201904],[201905]) 
	  ) AS Z

 

3-5) PIVOT 쿼리 수행

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

스트링쿼리를 EXEC 구문을 이용하여 수행하여 결과 테이블을 생성한다.

 

 

전체 스크립트

	-- 1. 매출 테이블 생성
	CREATE TABLE SALE
	(
	
		 YYYYMM		VARCHAR(6)			-- 년월
		,PRD_NAME	VARCHAR(200)		-- 품목
		,QTY		NUMERIC(18,3)		-- 수량
		,AMT		MONEY				-- 금액

	)

	-- 2. 매출 데이터 입력
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201901', 'A', 5, 5000)		-- 품목 A 19년 1월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201902', 'A', 10, 10000)		-- 품목 A 19년 2월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201903', 'A', 5, 10000)		-- 품목 A 19년 3월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201904', 'A', 10, 10000)		-- 품목 A 19년 4월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201905', 'A', 10, 10000)		-- 품목 A 19년 5월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201901', 'B', 20, 40000)		-- 품목 B 19년 1월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201902', 'B', 10, 20000)		-- 품목 B 19년 2월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201903', 'B', 5, 10000)		-- 품목 B 19년 3월 매출
	INSERT INTO SALE(YYYYMM, PRD_NAME, QTY, AMT) VALUES('201904', 'B', 10, 20000)		-- 품목 B 19년 4월 매출

	-- 변경전 테이블
	SELECT
		 PRD_NAME	품목			-- 품목
		,YYYYMM		매출월			-- 년월
		,SUM(AMT)	매출액			-- 금액
	FROM SALE
	GROUP BY PRD_NAME, YYYYMM

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

	-- 1. 임시테이블(#TEMP_TABLE)에 변경 전 테이블을 SELECT ... INTO 하여 저장한다.
	SELECT
		 PRD_NAME		-- 품목
		,YYYYMM			-- 년월
		,SUM(AMT)	AMT	-- 금액
	INTO #TEMP_TABLE
	FROM SALE
	GROUP BY PRD_NAME, YYYYMM


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

	SET @PIVOT_COLS = ''
	SET @TOTAL_SUM = ''
	SET @EXEC_QUERY =''
	SELECT @PIVOT_COLS = @PIVOT_COLS + '['+ YYYYMM +'],' 
		  ,@TOTAL_SUM = @TOTAL_SUM + 'ISNULL([' + YYYYMM + '],0) +'
	FROM (
			SELECT DISTINCT 
				YYYYMM
			FROM #TEMP_TABLE
		 ) AS A

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

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

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

 

품목에 대한 월별 자료를 PIVOT하여 보여주는 과정에 대해 설명을 해보았다.

 

이번 내용에서는 매출액을 월별로 PIVOT하여 보여주는 과정을 설명했는데, 이것을 조금 더 응용하면 2개이상(매출액, 매출수량 등)의 컬럼을 PIVOT하여 처리할 수도 있다.

 

여러컬럼 PIVOT하기 응용링크 ▼

2019/08/28 - [프로그래밍/MS-SQL] - MSSQL PIVOT 여러컬럼 동적으로 수행하기

 

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

이번 피드에서는 이전에 다루었던 피벗테이블의 응용인 여러컬럼을 피벗처리하는 방법에 대해 소개하도록 하겠다. 이전 피벗에 대한 내용을 보지 않고 왔다면 아래 링크를 따라 피벗하는 법을 확인 후 이번 피드를..

maelife.tistory.com

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

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

▼▼▼▼▼▼▼▼▼▼▼

 

이 댓글을 비밀 댓글로