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
여러분의 공감과 댓글이 큰 힘이됩니다 :)
▼▼▼▼▼▼▼▼▼▼▼
'프로그래밍 > MS-SQL' 카테고리의 다른 글
| MSSQL PIVOT 여러컬럼 동적으로 수행하기 (0) | 2019.08.28 |
|---|---|
| MSSQL PIVOT 동적으로 수행하기 (0) | 2019.08.21 |
| MSSQL 함수 작성법 (0) | 2019.08.06 |
| MSSQL 저장프로시저(StoredProcedure) 작성법 (0) | 2019.07.30 |
| MSSQL 커서(CURSOR) 사용법 (0) | 2019.07.30 |
| [MS-SQL] CRUD 자동 스크립트 작성 내용 공유 (0) | 2019.07.12 |
