DB

[MS-SQL] PIVOT(피봇) 기본 및 심화(동적 쿼리 생성)

떠돌이개발자

피봇(PIVOT)은 질의한 데이터에 대한 행의 집합을 열로 변환시켜 보여줄 때 사용하게 됩니다.  보통의 경우 통계 데이터를 추출하는 쿼리를 작성할 때 많이 사용되지요. 반대로 UNPIVOT은 열을 행으로 보여줄 때 사용해요.

 

테스트를 위해 테이블 및 데이터를 INSERT 합니다.

CREATE TABLE [dbo].[TB_TEST](
	[SEQ] [varchar](10) NOT NULL,
	[USER_NM][varchar](30) NOT NULL,
	[KOR] [varchar](3) NOT NULL,
	[MAT] [varchar](3) NOT NULL,
	[ENG] [varchar](3) NOT NULL,
	[CRT_DATE] [varchar](8) NOT NULL,	
 CONSTRAINT [PK_TB_TEST] PRIMARY KEY NONCLUSTERED 
(
	[SEQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
	, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 
 


INSERT INTO TB_TEST VALUES('001', '영희','100','90','80','20210101')
INSERT INTO TB_TEST VALUES('002', '영희','80','90','80','20210201') 
INSERT INTO TB_TEST VALUES('003', '영희','70','90','60','20210301')

INSERT INTO TB_TEST VALUES('004', '철수','100','100','100','20210101')
INSERT INTO TB_TEST VALUES('005', '철수','85','93','60','20210201') 
INSERT INTO TB_TEST VALUES('006', '철수','50','100','90','20210301')

INSERT INTO TB_TEST VALUES('007', '똘이','90','70','60','20210101')
INSERT INTO TB_TEST VALUES('008', '똘이','90','100','80','20210201') 
INSERT INTO TB_TEST VALUES('009', '똘이','90','70','100','20210301')

위 테스트 테이블을 조회하면 다음과 같은 결과를 볼 수 있는데요.

SELECT * FROM TB_TEST


본격적으로 피봇 사용법에 대해 알아봅니다. 

 

MS-SQL PIVOT(피봇) 기본 문법 사용 방법

SELECT * FROM 
( 피벗 대상 쿼리문 ) AS final
PIVOT ( 
	그룹합수(집계컬럼) FOR 피벗대상컬럼 IN ([피벗컬럼값] ...) 
) AS piv

날짜월(CRT_DATE) 칼럼 기준으로 영희, 철수, 똘이의 국어, 영어, 수학 점수를 보는 쿼리는 다음과 같아요. 

SELECT * FROM TB_TEST A
PIVOT (
	MIN(USER_NM) FOR CRT_DATE IN ([20210101],[20210201],[20210301])
) AS PIV

그룹합수 MIN을 사용했는데요, MAX를 사용해도 됩니다. 만약 점수들의 합이 필요하다면 SUM을 사용해도 되죠. 

피벗대상 칼럼 값의 IN 절 안에 데이터는 반드시 대괄호 “[ ]” 기호를 사용하여 처리해야합니다.  만약  IN( ‘20210101’,….) 타입으로 정의하게 되면 ‘20210101’ 근처의 구문이 잘못되었습니다. 라는 메시지를 받아보게 됩니다.

 

[쿼리 결과]


날짜 월이 행의 데이터에서 열(헤더)로 이동하게 되고, 행의 데이터인 사람의 이름이 열로 이동하여 출력하게 됩니다.

 

[참고사항]

그리고 중요한 점은 SELECT * FROM 절에 알리스 값 A를 주면

“열 접두사 ‘A’이(가) 쿼리에 사용된 테이블 이름 또는 별칭과 일치하지 않습니다.” 라는 오류와 직면하게 됩니다.

삽질금지!!


 

피봇 대상 칼럼이 2개 이상일 경우에 대한 처리 방법(다중 칼럼 피봇 처리)

오라클은 두 개의 열에 대해 PIVOT를 지원하지만, MSSQL은 지원하지 않아요.  이럴 때는 CONCAT함수를 사용하여 구분자를 포함하여 문자열로 합치는 작업을 한 후 SPLIT함수를 사용하여 추 후 분리가능합니다.

아래 스니펫 샘플 쿼리를 보시면 이해가 오실거에요.

SELECT * 
FROM 
(
	SELECT A.USER_NM
	, A.CRT_DATE
	, CONCAT(KOR,'/',MAT,'/', ENG) AS REUSLT_STR
	FROM TB_TEST A 
) FINAL
PIVOT (
	MAX(REUSLT_STR) FOR CRT_DATE IN ([20210101],[20210201],[20210301])
) AS PIV

[쿼리 실행결과]


 

MSSQL 동적 피봇 쿼리 생성 방법

1. IN 절에 들어갈 대상에 대한 문자열 생성을 위한 임시 테이블 생성 (쿼리로도 가능하지만, 이 작업이 편해요)

2. 피벗 칼럼값 INSERT

--임시 테이블 생성
DECLARE @TBL_MONTH TABLE (DATE_STR VARCHAR(8))

--INSERT
INSERT INTO @TBL_MONTH (DATE_STR)
SELECT DISTINCT CRT_DATE FROM TB_TEST

만약, 테이블 생성시 오류가 발생한다면, 이전에 만든 동일한 임시테이블을 삭제합니다.

IF EXISTS (
	SELECT * FROM tempdb.sys.sysobjects 
    WHERE name like '#TBL_MONTH%' 
    AND ftcatid = 0
    ) DROP TABLE #TBL_MONTH

3. 피봇 칼럼값 문자열 생성

DECLARE @P_QUERY NVARCHAR(MAX) = '';
DECLARE @PIVOT   NVARCHAR(MAX) = '';


SET @PIVOT  = STUFF((SELECT '],['+DATE_STR FROM @TBL_MONTH FOR XML PATH('')) + ']',1,1,'');
SET @PIVOT  = RIGHT(@PIVOT, LEN(@PIVOT) - 1);

SELECT @PIVOT


 

4.  동적 피봇 쿼리 작성 

문자열 타입으로 작성합니다.

DECLARE @P_QUERY NVARCHAR(MAX) = '';

SET @P_QUERY = '
	SELECT *
	FROM
	(
		SELECT A.USER_NM
		, A.CRT_DATE
		, CONCAT(KOR,''/'',MAT,''/'', ENG) AS REUSLT_STR
		FROM TB_TEST A 
	) FINAL
	PIVOT(
		MAX(REUSLT_STR) FOR CRT_DATE IN('+@PIVOT+')
	) AS PVT
	ORDER BY USER_NM ASC
'

--생성된 쿼리문 확인
SELECT @P_QUERY

5. EXECUTE 문을 사용하여 쿼리를 실행합니다.

EXECUTE(@P_QUERY)

 

[동적 피봇 쿼리 스크립트 전체]

IF EXISTS (
	SELECT * FROM tempdb.sys.sysobjects 
    WHERE name like '#TBL_MONTH%' 
    AND ftcatid = 0
    ) DROP TABLE #TBL_MONTH
    

DECLARE @TBL_MONTH TABLE (DATE_STR VARCHAR(8))

INSERT INTO @TBL_MONTH (DATE_STR)
SELECT DISTINCT CRT_DATE FROM TB_TEST


DECLARE @P_QUERY NVARCHAR(MAX) = '';
DECLARE @PIVOT   NVARCHAR(MAX) = '';


SET @PIVOT  = STUFF((SELECT '],['+DATE_STR FROM @TBL_MONTH FOR XML PATH('')) + ']',1,1,'');
SET @PIVOT  = RIGHT(@PIVOT, LEN(@PIVOT) - 1);


SET @P_QUERY = '
	SELECT *
	FROM
	(
		SELECT A.USER_NM
		, A.CRT_DATE
		, CONCAT(KOR,''/'',MAT,''/'', ENG) AS REUSLT_STR
		FROM TB_TEST A 
	) FINAL
	PIVOT(
		MAX(REUSLT_STR) FOR CRT_DATE IN('+@PIVOT+')
	) AS PVT
	ORDER BY USER_NM ASC
'

SELECT @P_QUERY
 

EXECUTE(@P_QUERY)

[동적쿼리 실행결과]


 

현업에서는 동적쿼리를 가지고 프로시저를 생성하는 방법으로 많이 사용해요. 날짜 검색기간 조건과 사용자 이름 등을 파리미터 값으로 받아서  피봇 쿼리 결과를 리턴하는 프로시저를 생성해 보세요.

피봇 결과 조회 프로시저(PROCEDURE) 생성

CREATE PROCEDURE SP_PIVOT_SAMPLE
(  
	@P_WORK_TP			VARCHAR(30) = 'Q',

	@P_DATE_FR			VARCHAR(8) = '',
	@P_DATE_TO			VARCHAR(8) = '',	
	@P_USER_NAME		VARCHAR(30)
	
,	@P_ERROR_CD		VARCHAR(30)		= ''	OUTPUT	-- 사용자 에러코드 리턴  
,	@P_ROW_COUNT	INT				= 0		OUTPUT	-- 실행/리턴하는 레코드행수 
,	@P_RETURN_STR	NVARCHAR(100)	= ''	OUTPUT	-- 사용자 지정 반환값
)
AS 
SET NOCOUNT ON  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN   	
    BEGIN TRY    
		IF @P_WORK_TP = 'Q_LIST'
		BEGIN
			IF EXISTS (SELECT * FROM tempdb.sys.sysobjects WHERE name like '#TBL_MONTH%' AND ftcatid = 0) DROP TABLE #TBL_MONTH

			DECLARE @TBL_MONTH TABLE (DATE_STR VARCHAR(8))

			INSERT INTO @TBL_MONTH (DATE_STR)
			SELECT DISTINCT CRT_DATE FROM TB_TEST


			DECLARE @P_QUERY NVARCHAR(MAX) = '';
			DECLARE @PIVOT   NVARCHAR(MAX) = '';


			SET @PIVOT  = STUFF((SELECT '],['+DATE_STR FROM @TBL_MONTH FOR XML PATH('')) + ']',1,1,'');
			SET @PIVOT  = RIGHT(@PIVOT, LEN(@PIVOT) - 1);


			SET @P_QUERY = '
				SELECT *
				FROM
				(
					SELECT A.USER_NM
					, A.CRT_DATE
					, CONCAT(KOR,''/'',MAT,''/'', ENG) AS REUSLT_STR
					FROM TB_TEST A 
				) FINAL
				PIVOT(
					MAX(REUSLT_STR) FOR CRT_DATE IN('+@PIVOT+')
				) AS PVT
				ORDER BY USER_NM ASC
			'
 
			EXECUTE(@P_QUERY)
		END

    END TRY
    BEGIN CATCH
        DECLARE @V_EXEC_INFO NVARCHAR(MAX);

		-- 실행 조건
		SELECT @V_EXEC_INFO = N'SP_PIVOT_SAMPLE '
				+ N'  @P_WORK_TP=N''' + ISNULL(@P_WORK_TP, '') + ''' '
				+ N'  @P_DATE_FR=N''' + ISNULL(@P_DATE_FR, '') + ''' '					
				+ N'  @P_DATE_TO=N''' + ISNULL(@P_DATE_TO, '') + ''' '						
				+ N', @P_USER_NAME=N''' + ISNULL(@P_USER_NAME, '') + ''' '

		-- 에러로그 기록
		INSERT SYS_PROC_ERR_LOG (ERR_PROC, ERR_CD, ERR_STA, ERR_MSG, EXEC_PROC_INFO)
		SELECT
			ERR_PROC = ERROR_PROCEDURE(),
			ERR_CD = CONVERT(VARCHAR(100), ERROR_NUMBER()),
			ERR_STA = ISNULL(SUBSTRING(@P_WORK_TP,1,1), 'X') + '|' + CONVERT(VARCHAR(100), ERROR_SEVERITY()) + '|' + CONVERT(VARCHAR(100), ERROR_STATE()) + '|' + CONVERT(VARCHAR(100), ERROR_LINE()),
			ERR_MSG = ERROR_MESSAGE(),
			EXEC_PROC_INFO = @V_EXEC_INFO
		;

		-- CRUD 에러코드 반환
		SELECT @P_ERROR_CD = (CASE SUBSTRING(@P_WORK_TP,1,1)
									WHEN 'Q' THEN 'ERR0006'				 -- 조회시 에러가 발생하였습니다. 
									WHEN 'N' THEN 'ERR0008'				 -- 등록시 오류가 발생하였습니다.
									WHEN 'U' THEN 'ERR0009'				 -- 수정시 오류가 발생하였습니다.
									WHEN 'D' THEN 'ERR0010'				 -- 삭제시 오류가 발생하였습니다.
									ELSE 'ERR0000'
								END);
    END CATCH;
END

 

프로시저 실행하는 방법

EXEC SP_PIVOT_SAMPLE 'Q_LIST','','',''

[프로시저 실행결과]


생성한 프로시저 삭제하는 방법

DROP PROCEDURE SP_PIVOT_SAMPLE

 

 

Related Posts

Leave a Reply

error: Content is protected !!