[SQL] PIVOT에 대해 알아보고, 꿀팁도 얻어가자.

 

PIVOT 이란?

식에 있는 한 열의 고유 값을 출력의 여러 열로 반환하여 테이블 반환 식을 순환합니다. 라고 MSDN에 설명되어져 있다.

하지만 조금 어렵게 느껴진다.

쉽게 설명해서, 식에 있는 한 열의 값들을 회전하여 테이블의 열(column)으로 지정하는 것. 라고 말하고 싶다.

이것도 어렵다면, 아래 그림으로 차근차근 설명해보겠다.

 

PIVOT 전

PIVOT 후

 

PIVOT 하기 전 데이터는 2013년부터 2020년까지, 월별 특정 갯수를 구한 쿼리이다.

'[year_value]에 있는 값인 2013~2020' 을 테이블의 열로 회전하려고 하는 것의 PIVOT의 목적이다.

위 그림이 PIVOT 하기 전/후 를 나타낸 것이므로, 그림으로 PIVOT의 목적을 이해하는 것이 더 효율적이라고 생각된다.

 

PIVOT 문법

SELECT 집계함수의 열 + 기준열의 값
FROM (
    SELECT 원본테이블
) AS TEMP
PIVOT (
    집계함수 FOR 기준열 IN (기준열의 값)
) AS PVT

 

PIVOT 문법 샘플

SELECT [month_value], [2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020]
FROM (
    SELECT [year_value], [month_value], [count_value]
    FROM #DATA
) AS TEMP
PIVOT (
    MIN([count_value]) FOR year_value IN ([2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020])
) AS PVT

 

[year_value]의 열을 기준으로 PIVOT 처리를 해주었다.

 

그리고 집계함수 MIN을 사용하였는데,

#DATA에는 연도별 월의 값이 1개만 존재하기에 SUM을 할 필요가 없기에 MIN을 사용해주었다.

나와 같이 특정한 값이 1개만 존재한다면, MIN/MAX를 사용하면 될 것이고 여러값이 존재한다면 SUM을 사용하면 된다.

 

그리고 주의해야할 점이라면, 7번째 라인에서 기준열의 값을 나열할때 꼭 [] 대괄호를 사용해서 넣어주어야 한다는 점이다. 대괄호를 사용하지 않으면 오류가 발생하니 이점 참고하자.

 

1번째 라인에서 예제에서는 모든 컬럼을 나열했지만, 모든 컬럼을 조회하는 * 을 사용해도 무관하다.

 

집계한 열이 2개 이상이라면?

count_value / count_value2 집계함수가 2개 존재할때

위 그림과 같이, [count_value] / [count_value2] 라는 두개의 열이 있다면 어떻게 해야할까..?

 

오라클에서는 두개의 열에 대해 집계 PIVOT 이 가능하지만, MSSQL에서는 불가능하다.

그렇기 때문에 MSSQL에서는 꼼수를 이용해서, 두개의 열에 대해 집계 PIVOT을 처리해주어야 한다.

 

SELECT [month_value], [2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020]
FROM (
    SELECT [year_value], [month_value], CONCAT([count_value], '/', [count_value2]) AS [total_count]
    FROM #DATA
) AS TEMP
PIVOT (
    MIN([total_count]) FOR year_value IN ([2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020])
) AS PVT

 

CONCAT 함수를 이용하여, 두 열을 합치면 가능하다.

CONCAT 함수를 사용할때, 구분자를 넣어서 나중에 SPLIT 함수를 이용하여 두 열을 나누어서 사용하면 된다.

 

contcat함수를 이용한 결과값

그리고 기준열의 값이 가변일 수도 있다.

기준열의 값이 가변이라면 정적 쿼리문이 아닌, 동적 쿼리문을 사용해서 처리해야한다.