[MSSQL] PIVOT 함수, 행을 열로 바꾸기 (크로스탭, 동적컬럼)

SQL Server에서 행을 열로 변환하기 위해서는 PIVOT() 함수를 이용하면 된다. 특히 그룹함수를 이용하여 통계를 추출할 때 많이 사용된다.

 

MSSQL PIVOT 함수

 

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

 

그룹함수는 SUM(), COUNT(), AVG() 등을 사용할 수 있다.

피벗컬럼값은 한번 지정하면 데이터가 존재하지 않아도 고정적으로 출력된다.

 

피벗컬럼값의 대괄호([ ])는 존재해야하며, FROM절과 PIVOT절의 별칭(result, pivot_result)은 꼭 붙여줘야 오류가 발생하지 않는다.

 

PIVOT 기본 사용법 - 직군별, 근무지별 급여 합계

SELECT *
  FROM (
         SELECT job
              , deptno
              , sal
           FROM emp
       ) AS result
 PIVOT ( 
         SUM(sal) FOR deptno IN ([10], [20], [30], [40]) 
       ) AS pivot_result
 ORDER BY job

 

근무지코드(10, 20, 30...)를 피벗 대상 컬럼으로 지정하면 조회된 데이터에 없는 근무지 코드도 출력되기 때문에, 존재하는 근무지코드만 출력하기 위해서는 동적쿼리문을 사용해야 한다.

 

동적 PIVOT - 직군별, 년월별 입사 건수 (존재하는 년월만 표시)

DECLARE @COLUMNS NVARCHAR(MAX)
DECLARE @SQL     NVARCHAR(MAX)

SET @COLUMNS = ''

SELECT @COLUMNS = @COLUMNS + '[' + yyyymm + '],'
  FROM (
         SELECT DISTINCT
                CONVERT(NVARCHAR(6), hiredate, 112) AS yyyymm
           FROM emp
          WHERE hiredate BETWEEN '1981-01-01' AND '1981-06-30'
       ) AS months
 ORDER BY yyyymm

SET @COLUMNS = LEFT(@COLUMNS, LEN(@COLUMNS) - 1)

SET @SQL = ' 
    SELECT *
      FROM (
             SELECT empno
                  , job
                  , CONVERT(NVARCHAR(6), hiredate, 112) AS yyyymm
               FROM emp
              WHERE hiredate BETWEEN ''1981-01-01'' AND ''1981-06-30''
            ) AS result
      PIVOT ( 
              COUNT(empno) 
              FOR yyyymm IN (' + @COLUMNS + ') 
            ) AS pivot_result 
'

EXEC(@SQL)

 

존재하는 년월만 중복을 제거하여 동적쿼리문에 적용하면, 피벗 컬럼 값을 지정하지 않고 조회한 데이터에 존재하는 값만 피벗 컬럼으로 출력된다.

 

사용 예제

 

 

 

 

사용_예제_쿼리.txt

0.01MB

 

[MSSQL] UNPIVOT 함수, 열을 행으로 바꾸기 (가로를 세로로)