시스매

데이터 분석의 핵심 기술 PIVOT 완벽 핸드북 엑셀부터 SQL까지

17 min read

2025-08-24 13:28

데이터 분석의 핵심 기술 PIVOT 완벽 핸드북 엑셀부터 SQL까지

데이터의 바다에서 길을 잃지 않고 원하는 정보를 정확히 찾아내기란 쉽지 않습니다. 수많은 행과 열로 이루어진 데이터를 그저 바라보는 것만으로는 숨겨진 의미를 파악하기 어렵죠. 이때, 마치 마법처럼 데이터의 형태를 바꾸어 새로운 관점을 제시하는 강력한 도구가 있습니다. 바로 **PIVOT(피벗)**입니다.

PIVOT은 데이터를 요약하고 재구성하여 복잡한 데이터 속에서 패턴과 인사이트를 쉽게 발견하도록 돕는 핵심적인 데이터 분석 기술입니다. 엑셀의 피벗 테이블부터 데이터베이스의 PIVOT 연산자까지, PIVOT은 데이터 분석가, 마케터, 기획자 등 데이터를 다루는 모든 사람에게 필수적인 역량으로 자리 잡았습니다.

이 핸드북에서는 PIVOT이 왜 만들어졌는지, 어떤 구조로 이루어져 있으며 어떻게 활용할 수 있는지, 그리고 더 나아가 심화적인 내용까지 포괄적으로 다룰 것입니다. PIVOT의 개념부터 실전 활용법까지, 이 글 하나로 완벽하게 마스터해 보세요.

1. PIVOT 왜 만들어졌을까? 탄생 배경과 필요성

PIVOT의 개념을 이해하기 위해 먼저 데이터가 일반적으로 어떻게 저장되는지 살펴볼 필요가 있습니다. 데이터는 보통 ‘긴 형식(Long Format)’ 또는 ‘정규화된(Normalized)’ 형태로 저장됩니다. 이는 데이터의 중복을 최소화하고 일관성을 유지하기 위한 가장 효율적인 방법이기 때문입니다.

예를 들어, 여러 상점의 일별 제품 판매 데이터를 생각해 보겠습니다. 이 데이터는 보통 다음과 같은 ‘긴 형식’으로 저장됩니다.

날짜상점명제품명판매량
2023-10-01강남점사과10
2023-10-01강남점바나나15
2023-10-01홍대점사과12
2023-10-01홍대점오렌지8
2023-10-02강남점사과11
2023-10-02홍대점바나나20

이러한 ‘긴 형식’ 데이터는 저장하고 관리하기에는 효율적이지만, 우리가 원하는 정보를 한눈에 파악하기는 어렵습니다. 예를 들어 “강남점의 제품별 총 판매량은 얼마일까?” 또는 “날짜별로 각 상점의 판매량 추이는 어떻게 될까?”와 같은 질문에 답하려면 데이터를 여러 번 필터링하고 그룹화해야 하는 번거로움이 있습니다.

바로 이러한 문제점을 해결하기 위해 PIVOT이 등장했습니다. PIVOT은 ‘긴 형식’의 데이터를 **‘넓은 형식(Wide Format)‘**으로 변환하여 우리가 보고 싶은 형태로 데이터를 재구성해 줍니다. 즉, 특정 열(Row)의 고유한 값들을 새로운 열(Column)로 만들어 데이터를 요약하고 비교하기 쉽게 만들어주는 것입니다.

위의 판매 데이터를 PIVOT을 사용하여 ‘상점명’을 기준으로 재구성하면 다음과 같은 ‘넓은 형식’의 표를 얻을 수 있습니다.

제품명강남점홍대점
사과2112
바나나1520
오렌지08

어떤가요? 훨씬 더 직관적이지 않나요? 각 상점의 제품별 판매량을 한눈에 비교할 수 있게 되었습니다. 이처럼 PIVOT은 데이터의 가독성과 분석 용이성을 극대화하기 위해 탄생한 필수적인 데이터 변환 기술입니다.

2. PIVOT의 구조 해부하기: 무엇으로 이루어져 있나?

PIVOT은 크게 세 가지 핵심 요소로 구성됩니다. 이 세 가지 요소의 관계를 이해하면 어떤 데이터든 자유자재로 PIVOT을 활용할 수 있게 됩니다.

  1. 행 (Rows): 분석의 기준이 되는 축입니다. 데이터를 어떤 기준으로 그룹화하여 세로 방향으로 나열할지 결정합니다. 위의 예시에서는 ‘제품명’이 행이 됩니다. ‘날짜’를 행으로 설정하면 일별 판매량 데이터를 볼 수도 있습니다.
  2. 열 (Columns): 기존 행에 있던 고유 값들이 변환되어 새로운 열을 구성하는 축입니다. 어떤 값을 기준으로 데이터를 가로 방향으로 펼쳐 비교할지 결정합니다. 위의 예시에서는 ‘상점명’의 고유 값인 ‘강남점’과 ‘홍대점’이 새로운 열이 되었습니다.
  3. 값 (Values): 행과 열이 교차하는 지점에 채워질 데이터입니다. 주로 합계, 평균, 개수 등 **집계 함수(Aggregation Function)**를 통해 계산된 요약 값이 들어갑니다. 위의 예시에서는 ‘판매량’의 합계가 값으로 사용되었습니다. 만약 평균 판매량을 보고 싶다면 집계 함수를 ‘평균’으로 변경하면 됩니다.

이 세 가지 요소를 레고 블록처럼 조합하여 원하는 형태의 보고서를 만드는 과정이 바로 PIVOT입니다.

PIVOT 구성 요소설명예시
행 (Rows)데이터를 그룹화하는 기준 (세로 축)제품명, 날짜, 고객 등급
열 (Columns)데이터를 펼쳐서 비교하는 기준 (가로 축)상점명, 지역, 월(Month)
값 (Values)요약될 숫자 데이터 (교차 지점)판매량, 매출액, 고객 수
집계 함수값을 요약하는 방법합계(SUM), 평균(AVG), 개수(COUNT)

이 구조를 이해하면 “나는 [행] 별로 [열] 에 따른 [값] 의 [집계] 를 보고 싶어”라는 문장으로 원하는 분석 보고서를 명확하게 정의할 수 있습니다.

3. PIVOT 실전 활용법: 엑셀과 SQL

PIVOT은 다양한 데이터 분석 도구에서 사용됩니다. 가장 대표적인 두 가지, 엑셀(Excel)과 SQL(Structured Query Language)에서의 활용법을 알아보겠습니다.

3.1. 엑셀 피벗 테이블: 누구나 쉽게 시작하는 데이터 분석

엑셀의 피벗 테이블은 코딩 없이 마우스 클릭만으로 PIVOT을 구현할 수 있는 가장 직관적이고 강력한 기능입니다.

피벗 테이블 생성 과정:

  1. 데이터 선택: 원본 데이터 범위 전체를 선택합니다.

  2. 삽입 > 피벗 테이블: 엑셀 메뉴에서 [삽입] 탭의 [피벗 테이블]을 클릭합니다.

  3. 피벗 테이블 필드 설정: 화면 오른쪽에 나타나는 ‘피벗 테이블 필드’ 창에서 위에서 설명한 PIVOT의 세 가지 요소(행, 열, 값)를 각각 원하는 필드로 드래그 앤 드롭(Drag and Drop)합니다.

    • ‘제품명’을 행 영역으로

    • ‘상점명’을 열 영역으로

    • ‘판매량’을 값 영역으로

  4. 값 필드 설정: ‘값’ 영역에 추가된 필드를 클릭하여 ‘값 필드 설정’에서 합계, 평균, 개수 등 원하는 집계 함수를 선택합니다.

단 몇 번의 클릭만으로 복잡했던 ‘긴 형식’의 데이터가 한눈에 들어오는 ‘넓은 형식’의 요약 보고서로 재탄생합니다. 또한, 필터를 추가하거나 행과 열의 위치를 바꾸는 등 유연하게 보고서 형태를 변경하며 다각적인 분석을 수행할 수 있습니다.

3.2. SQL PIVOT 연산자: 데이터베이스에서 직접 데이터 재구성

대용량 데이터를 다루는 데이터베이스 환경에서는 SQL의 PIVOT 연산자를 사용하여 데이터를 직접 변환할 수 있습니다. 이는 분석을 위해 데이터를 다른 도구로 옮기는 과정을 생략하여 효율성을 높여줍니다.

SQL Server, Oracle 등 여러 데이터베이스 시스템이 PIVOT 연산자를 지원합니다. 다음은 SQL Server에서의 PIVOT 구문 예시입니다.

SELECT
    제품명,
    [강남점], -- PIVOT될 열 이름을 명시
    [홍대점]
FROM
    (
        -- PIVOT을 적용할 원본 데이터 선택
        SELECT
            제품명,
            상점명,
            판매량
        FROM
            SalesData
    ) AS SourceTable
PIVOT
(
    SUM(판매량) -- 값(Values)에 적용할 집계 함수
    FOR 상점명 IN ([강남점], [홍대점]) -- 열(Columns)로 만들 값들을 지정
) AS PivotTable;

SQL PIVOT 구문 해설:

  • FROM ( ... ) AS SourceTable: PIVOT을 적용할 원본 데이터를 서브쿼리(Subquery) 형태로 지정합니다. 여기에는 행, 열, 값이 될 필드가 모두 포함되어야 합니다.

  • PIVOT ( ... ) AS PivotTable: PIVOT 연산의 핵심 부분입니다.

    • SUM(판매량): 값으로 사용할 ‘판매량’ 열과 집계 함수 SUM을 지정합니다.

    • FOR 상점명 IN ([강남점], [홍대점]): ‘상점명’ 열의 값들 중에서 새로운 열 이름이 될 값(‘강남점’, ‘홍대점’)을 명시적으로 나열합니다.

SQL PIVOT은 엑셀보다 사용법이 복잡하지만, 대규모 데이터 처리 자동화 및 다른 시스템과의 연동에 매우 강력한 성능을 발휘합니다.

4. PIVOT 심화 탐구: UNPIVOT과 동적 PIVOT

PIVOT의 기본 개념을 익혔다면, 이제 그 반대 과정과 더 유연한 활용법에 대해 알아볼 차례입니다.

4.1. UNPIVOT: 넓은 데이터를 다시 길게

**UNPIVOT(언피벗)**은 PIVOT과 정반대의 작업을 수행합니다. 즉, ‘넓은 형식’의 데이터를 다시 ‘긴 형식’으로 되돌리는 것입니다.

UNPIVOT이 필요한 경우:

  • 여러 열에 걸쳐 분산된 데이터를 하나의 열로 정규화하여 데이터베이스에 저장하거나 관리하고 싶을 때

  • PIVOT된 형태의 데이터를 다른 분석 도구나 시각화 라이브러리에서 사용하기 위해 ‘긴 형식’으로 변환해야 할 때

  • 데이터 정제 및 전처리 과정에서 데이터 구조를 표준화할 때

PIVOT된 데이터:

제품명강남점홍대점
사과2112
바나나1520

UNPIVOT 결과:

제품명상점명판매량
사과강남점21
사과홍대점12
바나나강남점15
바나나홍대점20

SQL에서는 UNPIVOT 연산자를 사용하여 이 변환을 수행할 수 있습니다.

4.2. 동적 PIVOT (Dynamic PIVOT)

앞서 살펴본 SQL PIVOT 구문에는 한 가지 제약이 있습니다. 바로 열이 될 값([강남점], [홍대점])을 직접 코드에 명시해야 한다는 점입니다. 만약 새로운 ‘부산점’이 추가된다면 어떻게 해야 할까요? 매번 SQL 코드를 수정해야만 합니다.

이러한 불편함을 해결하는 것이 동적 PIVOT입니다. 동적 PIVOT은 열이 될 고유 값들을 데이터베이스에서 직접 조회하여 PIVOT 쿼리를 동적으로 생성하고 실행하는 프로그래밍 기법입니다.

주로 변수에 열 이름 목록을 문자열 형태로 저장한 뒤, 전체 SQL 쿼리를 하나의 문자열로 조합하여 EXEC 또는 sp_executesql과 같은 명령어로 실행하는 방식으로 구현합니다. 구현이 다소 복잡하지만, 열 값이 계속 변하거나 추가되는 상황에 유연하게 대처할 수 있어 실무에서 매우 유용하게 사용됩니다.

5. PIVOT을 넘어: 데이터 분석의 무한한 가능성

지금까지 PIVOT의 모든 것을 살펴보았습니다. PIVOT은 단순히 데이터의 행과 열을 바꾸는 기술을 넘어, 데이터를 바라보는 관점을 바꾸는 사고방식입니다. 복잡하게 얽힌 데이터의 실타래를 풀어 우리가 원하는 인사이트를 명확한 형태로 재구성해 주는 PIVOT은 데이터 기반 의사결정의 첫걸음이라 할 수 있습니다.

엑셀의 피벗 테이블로 데이터 분석에 입문하고, 더 나아가 SQL PIVOT을 통해 대용량 데이터를 자유자재로 다룰 수 있게 된다면, 여러분은 데이터 전문가로서 한 단계 더 성장할 수 있을 것입니다.

오늘 배운 내용을 바탕으로 여러분의 데이터를 직접 PIVOT 해보세요. 이전에는 보이지 않았던 새로운 패턴과 놀라운 인사이트가 여러분을 기다리고 있을 것입니다.

레퍼런스(References)

PIVOT

그래프 뷰

백링크