MSSQL PIVOT, UNPIVOT 사용법 및 특징

PIVOT은 검색된 데이터의 행 집합을 열로 변환시켜 결과 data를 보여주고 UNPIVOT은 열의 데이터를 행으로 변환해서 출력합니다.

 

PIVOT, UNPIVOT 설명 및 장점, 단점

PIVOT 설명.

• PIVOT 함수는 데이터의 행을 열로 변환하는 데 사용됩니다.
• PIVOT에는 집계함수를 사용하여 데이터의 결과를 출력합니다.
• 집계함수는 sum(), count(), ave() 등을 사용할 수 있습니다.
• PIVOT 시키는 대상은 FOR를 이용하여 지정할 수 있습니다.
• IN() 절 이후에 나오는 행은 열로 변환할 대상입니다.

UNPIVOT 설명.

• UNPIVOT은 PIVOT의 반대개념으로 생각하면 이해하기 쉽습니다.
• UNPIVOT 함수는 데이터의 열을 행 집합으로 변환하는 데 사용됩니다.
• IN() 절에 지정한 열을 FOR 문하고 UNPIVOT을 사용하여 하나의 행으로 지정합니다.
• 현업에서 사용하는 경우는 많지 않지만, 개념은 이해하고 넘어갔으면 합니다.

PIVOT, UNPIVOT 장점.

• PIVOT은 집계함수를 이용하여 데이터를 단순, 간결하게 출력할 수 있습니다.
• UNPIVOT은 data를 세분화하여 열로 data를, 분석력을 향상할 수 있습니다.
• PIVOT 하고 UNPIVOT함수는 복잡한 query를 단순하게 작업할 수 있습니다.

PIVOT, UNPIVOT 단점.

• PIVOT 함수는 정의된 열을 필요하므로 정적으로만 가능합니다. 물론 동적 SQL 사용할 수 있지만 열에 대한 제한이 있습니다.
• PIVOT, UNPIVOT함수는 대량의 data를 처리할 때는 성능상 문제를 발생시키고 있습니다.
• PIVOT, UNPIVOT로 작성한 query는 생각보다 복잡해서 소스 분석 및 작성 시 많은 시간이 필요할 수 있습니다.

PIVOT, UNPIVOT 사용 방법

PIVOT 사용법.

• DeptInfoForPivot 테이블에 저장된 data를 이용합니다.
• PIVOT 함수를 이용하여 행으로 지정된 급여를 열로 변화하여 지점별 급여의 sum을 구하세요.
• 소스코드입니다.
  
  

DROP TABLE IF EXISTS DeptInfoForPivot;
CREATE TABLE DeptInfoForPivot
(
	EmpId  SMALLINT NOT NULL, 
	DeptID SMALLINT NOT NULL, 
	DeptName NVARCHAR(40) NOT NULL,	 
	MonthPay INT NOT NULL 	 
); 
  
 INSERT INTO DeptInfoForPivot VALUES 
(100,10 ,'태평양지점팀','100'),
(101,10 ,'태평양지점팀','110'),
(102,10 ,'태평양지점팀','120'),
(200,20 ,'대서양지점','400'),
(300,30 ,'인도양지점','500'),
(400,40 ,'미국지점','700') ;
--  PIVOT
SELECT *
FROM (
	SELECT DeptName, DeptID, MonthPay
	FROM DeptInfoForPivot
) AS result
PIVOT (
	SUM(MonthPay) FOR DeptID IN ([10], [20], [30], [40], [50])
) AS pivot_result
ORDER BY DeptName;

• 실습결과입니다.

PIVOT 사용법 예제 입니다.

 

UNPIVOT 사용법.

• DeptInfoForUnPivot 테이블에 저장된 data를 이용합니다.
• UNPIVOT 함수를 이용하여 Dept_10, Dept_20, Dept_30, Dept_40의 열을 행으로 출력하세요.
• 소스코드입니다.

DROP TABLE IF EXISTS DeptInfoForUnPivot;
CREATE TABLE DeptInfoForUnPivot
(
	EmpId  SMALLINT NOT NULL, 
	DeptID SMALLINT NOT NULL, 
	DeptName NVARCHAR(40) NOT NULL,	 
	MonthPay INT NOT NULL,
	Dept_10 NVARCHAR(40) NULL,	 
	Dept_20 NVARCHAR(40) NULL,	 
	Dept_30 NVARCHAR(40) NULL,	 
	Dept_40 NVARCHAR(40) NULL,	 	 
); 
 INSERT INTO DeptInfoForUnPivot VALUES 
(100,10 ,'태평양지점팀','100','Dept_1X','','',''),
(101,10 ,'태평양지점팀','101','Dept_1X','','',''),
(102,10 ,'태평양지점팀','102','Dept_1X','','',''),
(200,20 ,'대서양지점','200','','Dept_2X','',''),
(300,30 ,'인도양지점','300','','','Dept_3X',''),
(400,40 ,'미국지점','400','','','','Dept_4X') ;

-- UNPIVOT
SELECT DeptName,DeptTile,unPivotRow
FROM (
	SELECT DeptName,Dept_10, Dept_20, Dept_30, Dept_40
	FROM DeptInfoForUnPivot
) AS result
UNPIVOT (
	unPivotRow FOR DeptTile IN (Dept_10, Dept_20, Dept_30, Dept_40)
) AS unpivot_result
WHERE unPivotRow <> ''
order by unPivotRow, DeptTile;

• 실습결과입니다.

UNPIVOT 사용법 코드 입니다.

PIVOT, UNPIVOT 마무리하며

• MSSQL은 UNPIVOT 및 PIVOT 함수를 통해 데이터에 대한 변환 기능을 제공하지만 자주 사용하지 않습니다.
• 간단한 PIVOT 기능은 CASE 문 사용을 추천합니다.
• PIVOT 대상의 열값이 동적으로 변경되는 경우는 동적 SQL을 이용하면 됩니다.