시스매

데이터를 길게 늘리는 마법 UNPIVOT 완벽 핸드북

18 min read

2025-09-01 01:25

  • UNPIVOT은 테이블의 여러 열(column)을 행(row)으로 변환하여 데이터를 정규화하는 강력한 기능입니다.

  • 넓은 형태(Wide Format)의 데이터를 분석하기 쉬운 긴 형태(Long Format)로 재구성할 때 필수적입니다.

  • SQL Server, Oracle 등에서 기본 제공되며, 다른 DBMS에서는 CROSS APPLY나 UNION ALL 등으로 유사한 결과를 얻을 수 있습니다.

데이터를 길게 늘리는 마법 UNPIVOT 완벽 핸드북

데이터를 다루다 보면, 마치 잘 정리된 보고서처럼 한눈에 보기 좋게 펼쳐진 테이블을 마주할 때가 많습니다. 각 월, 각 분기, 각 제품 카테고리가 별도의 열로 존재하여 현재 상황을 파악하기에는 더할 나위 없이 편리하죠. 하지만 이 편리함은 데이터를 ‘분석’하려는 순간, 커다란 장벽이 되기도 합니다. “올해 총매출은 얼마지?”, “가장 매출이 높았던 분기는 언제지?” 와 같은 질문에 답하기 위해 수많은 열을 더하고 비교해야 하는 번거로움을 겪어보셨을 겁니다.

바로 이 문제를 해결하기 위해 등장한 마법 같은 기능이 UNPIVOT입니다. UNPIVOT은 넓게 펼쳐진 데이터의 열들을 차곡차곡 쌓아 올려, 분석하기 용이한 긴 형태의 데이터로 변환해주는 강력한 도구입니다. 이 핸드북을 통해 UNPIVOT이 왜 필요하며, 어떻게 사용하고, 어떤 상황에서 우리의 데이터 분석을 한 단계 업그레이드해 주는지 완벽하게 파헤쳐 보겠습니다.

1. UNPIVOT, 왜 만들어졌을까요? (탄생 배경)

UNPIVOT의 필요성을 이해하려면 먼저 ‘넓은 데이터(Wide Format)‘와 ‘긴 데이터(Long Format)‘의 차이점을 알아야 합니다.

“넓은 데이터” vs “긴 데이터”

  • 넓은 데이터 (Wide Format): 마치 우리가 엑셀에서 흔히 보는 피벗 테이블과 같습니다. 하나의 관측 대상(예: 한 명의 학생)에 대한 여러 속성(예: 국어 점수, 영어 점수, 수학 점수)이 각각의 ‘열’로 표현됩니다. 데이터를 요약해서 보거나 사람의 눈으로 읽기에는 매우 직관적입니다.
StudentIDKoreanEnglishMath
101908595
102759288
  • 긴 데이터 (Long Format): 데이터베이스가 선호하는 형태로, ‘Tidy Data’ 원칙을 따릅니다. 각 열은 하나의 변수(속성)를, 각 행은 하나의 관측치를 나타냅니다. 위의 학생 점수 데이터를 긴 형태로 바꾸면 다음과 같습니다.
StudentIDSubjectScore
101Korean90
101English85
101Math95
102Korean75
102English92
102Math88

넓은 데이터가 가진 분석의 한계

넓은 데이터는 보기에는 좋지만, 분석가에게는 몇 가지 심각한 문제를 안겨줍니다.

  1. 집계의 어려움: 학생 101의 ‘평균 점수’를 구하려면 (Korean + English + Math) / 3 과 같이 모든 과목 열을 직접 쿼리에 명시해야 합니다. 만약 ‘Science’ 과목이 추가된다면, 쿼리 자체를 수정해야 하는 유지보수의 악몽이 시작됩니다.

  2. 유연성 부족: “가장 높은 점수를 받은 과목은 무엇인가?” 또는 “과목별 평균 점수는 얼마인가?” 와 같은 질문에 답하기 매우 어렵습니다. 데이터를 GROUP BY 하거나 정렬할 기준이 되는 ‘과목’이라는 열이 없기 때문입니다.

  3. 확장성 문제: 새로운 과목이나 월별 데이터가 추가될 때마다 테이블의 구조, 즉 스키마가 변경됩니다. 이는 데이터베이스 설계 관점에서 매우 비효율적입니다.

UNPIVOT은 바로 이 넓은 데이터를 긴 데이터로 변환하여, 위에서 언급한 모든 문제를 해결해주는 해결사로 등장했습니다. 열에 흩어져 있던 데이터를 행으로 변환함으로써, 데이터는 비로소 분석을 위한 유연성과 확장성을 갖추게 됩니다.

2. UNPIVOT의 구조 파헤치기 (기본 문법)

UNPIVOT은 테이블의 열을 행으로 바꾸는 연산자로, 주로 FROM 절에서 테이블 이름 뒤에 사용됩니다. 그 구조는 생각보다 간단하며, 몇 가지 핵심 요소만 이해하면 금방 익숙해질 수 있습니다.

기본 뼈대 (SQL Server / Oracle 기준)

SQL

SELECT
    -- 고정할 열(들)
    , 새로운_속성_열
    , 새로운_값_열
FROM
    (원본_테이블) AS SourceTable
UNPIVOT
(
    새로운_값_열 FOR 새로운_속성_열 IN (원본_열1, 원본_열2, ...)
) AS UnpivotTable;

핵심 구성 요소

  • 새로운_값_열 (value_column): 행으로 변환될 원본 열들(예: Korean, English, Math 열)의 ‘값’(예: 90, 85, 95)이 저장될 새로운 열의 이름입니다. 보통 Score, Amount, Quantity 와 같이 값의 의미를 나타내는 이름을 사용합니다.

  • 새로운_속성_열 (category_column): 행으로 변환될 원본 열들의 ‘이름’(예: ‘Korean’, ‘English’, ‘Math’)이 저장될 새로운 열의 이름입니다. Subject, Month, Category 처럼 속성의 종류를 나타내는 이름을 사용합니다.

  • IN (원본_열1, 원본_열2, ...): UNPIVOT을 적용하여 행으로 바꿀 대상 열들의 목록을 명시적으로 나열하는 부분입니다.

간단한 예제

월별 제품 판매량 데이터를 변환하는 예시를 통해 구조를 살펴보겠습니다.

BEFORE: 넓은 데이터 ProductSales 테이블

ProductIDSales_JanSales_FebSales_Mar
A100150017001650
B200210020002200

이 데이터를 제품별, 월별 판매량으로 분석하기 위해 UNPIVOT을 적용해 보겠습니다.

SQL

SELECT
    ProductID,
    SaleMonth,
    SaleAmount
FROM
    ProductSales
UNPIVOT
(
    SaleAmount FOR SaleMonth IN (Sales_Jan, Sales_Feb, Sales_Mar)
) AS MonthlySales;

  • SaleAmount: Sales_Jan, Sales_Feb 등의 열에 있던 값(1500, 1700 등)이 담길 새로운 값 열입니다.

  • SaleMonth: IN 절에 명시된 열의 이름(‘Sales_Jan’, ‘Sales_Feb’ 등)이 담길 새로운 속성 열입니다.

  • ProductID: UNPIVOT의 대상이 아니므로 기존 구조를 그대로 유지하며, 변환된 모든 행에 반복해서 나타납니다.

AFTER: 긴 데이터

ProductIDSaleMonthSaleAmount
A100Sales_Jan1500
A100Sales_Feb1700
A100Sales_Mar1650
B200Sales_Jan2100
B200Sales_Feb2000
B200Sales_Mar2200

이제 GROUP BY SaleMonth 로 월별 총매출을 구하거나, WHERE ProductID = 'A100' 으로 특정 제품의 월별 판매 추이를 보는 등 훨씬 유연한 분석이 가능해졌습니다.

3. 실전! UNPIVOT 사용법 (예제와 활용)

이제 실제 시나리오에서 UNPIVOT을 어떻게 활용할 수 있는지 몇 가지 구체적인 예제를 통해 알아보겠습니다.

예제 1: 분기별 제품 판매량 분석

분기별 판매 실적이 열로 정리된 테이블이 있습니다.

QuarterlySales 테이블

| ProductName | Sales_Q1 | Sales_Q2 | Sales_Q3 | Sales_Q4 |

| :---------- | :------- | :------- | :------- | :------- |

| Keyboard | 550 | 620 | 580 | 710 |

| Mouse | 800 | 750 | 820 | 900 |

| Monitor | 300 | 350 | 330 | 400 |

가장 높은 판매량을 기록한 분기를 찾거나 분기별 평균 판매량을 계산하고 싶습니다.

SQL

SELECT
    ProductName,
    Quarter,
    Sales
FROM
    QuarterlySales
UNPIVOT
(
    Sales FOR Quarter IN (Sales_Q1, Sales_Q2, Sales_Q3, Sales_Q4)
) AS SalesByQuarter
ORDER BY
    Sales DESC;

이 쿼리는 모든 제품의 분기별 판매량을 긴 형태로 변환한 뒤, 판매량(Sales) 기준으로 내림차순 정렬합니다. 결과의 최상단 행을 보면 어느 제품이 어느 분기에 가장 높은 판매고를 올렸는지 즉시 파악할 수 있습니다.

예제 2: NULL 값 처리하기 (INCLUDE NULLS)

기본적으로 UNPIVOT 연산은 변환 대상 열의 값이 NULL인 경우 해당 행을 결과에서 제외합니다. 하지만 때로는 판매 실적이 없는(NULL) 경우도 분석에 포함해야 의미가 있을 수 있습니다.

SalesWithNull 테이블

| ProductID | Sales_2023 | Sales_2024 | Sales_2025 |

| :-------- | :--------- | :--------- | :--------- |

| P01 | 100 | 120 | NULL |

| P02 | 80 | NULL | 95 |

기본 UNPIVOT 쿼리를 실행하면 총 4개의 행만 반환되며, NULL이었던 P01의 2025년 데이터와 P02의 2024년 데이터는 사라집니다.

SQL

-- SQL Server에서 NULL을 포함하는 UNPIVOT
SELECT
    ProductID,
    Year,
    Sales
FROM
    SalesWithNull
UNPIVOT INCLUDE NULLS -- 이 옵션을 추가합니다.
(
    Sales FOR Year IN (Sales_2023, Sales_2024, Sales_2025)
) AS YearlySales;

INCLUDE NULLS 옵션을 사용하면 NULL 값도 결과에 포함되어 총 6개의 행이 모두 반환됩니다. 이를 통해 특정 연도에 판매 기록이 아예 없었다는 사실을 명확하게 분석할 수 있습니다. (이 옵션은 DBMS에 따라 지원 여부가 다를 수 있습니다.)

4. UNPIVOT 심화 탐구 및 대안

UNPIVOT은 매우 유용하지만 모든 상황에 대한 만능 해결책은 아닙니다. 더 복잡한 시나리오를 다루기 위한 대안과 UNPIVOT의 한계를 알아보겠습니다.

영혼의 단짝, PIVOT

PIVOT과 UNPIVOT은 정반대의 작업을 수행하는 한 쌍의 연산자입니다. PIVOT이 긴 데이터를 넓게 펼쳐 요약 보고서를 만든다면, UNPIVOT은 그 보고서를 다시 분석 가능한 긴 데이터로 되돌립니다. 이 둘의 관계를 이해하면 데이터를 자유자재로 변형하는 시야를 가질 수 있습니다.

UNPIVOT이 없다면? (대체 기술)

모든 데이터베이스 시스템이 UNPIVOT을 지원하는 것은 아닙니다. 혹은 UNPIVOT으로 해결하기 어려운 복잡한 변환이 필요할 수도 있습니다. 이럴 때 사용하는 대표적인 대안은 다음과 같습니다.

  1. UNION ALL

    가장 고전적이고 어떤 데이터베이스에서든 사용할 수 있는 보편적인 방법입니다. SELECT 문을 여러 번 작성하여 UNION ALL로 합치는 방식입니다.

    SQL

    SELECT ProductName, 'Sales_Q1' AS Quarter, Sales_Q1 AS Sales FROM QuarterlySales
UNION ALL
SELECT ProductName, 'Sales_Q2' AS Quarter, Sales_Q2 AS Sales FROM QuarterlySales
UNION ALL
SELECT ProductName, 'Sales_Q3' AS Quarter, Sales_Q3 AS Sales FROM QuarterlySales
UNION ALL
SELECT ProductName, 'Sales_Q4' AS Quarter, Sales_Q4 AS Sales FROM QuarterlySales;

    • 장점: 이해하기 쉽고 모든 SQL 환경에서 작동합니다.

    • 단점: 변환할 열이 많아질수록 쿼리가 매우 길어지고, 원본 테이블을 여러 번 스캔할 수 있어 대용량 데이터에서는 성능이 저하될 수 있습니다.

  2. CROSS APPLY (SQL Server)

    SQL Server 사용자라면 UNPIVOT보다 훨씬 강력하고 유연한 CROSS APPLY를 고려해볼 수 있습니다. VALUES 구문과 함께 사용하면 여러 열 그룹을 동시에 처리하는 등 복잡한 UNPIVOT 작업을 손쉽게 수행할 수 있습니다.

    예를 들어, 분기별 ‘판매량(Sales)‘과 ‘판매 개수(Units)‘가 모두 열로 있는 경우를 생각해 봅시다.

    | ProdName | Q1_Sales | Q1_Units | Q2_Sales | Q2_Units |

    이런 구조는 표준 UNPIVOT으로 한 번에 처리하기 까다롭지만, CROSS APPLY로는 간단하게 해결됩니다.

    SQL

    SELECT
    t.ProdName,
    c.Quarter,
    c.Sales,
    c.Units
FROM
    ComplexSales AS t
CROSS APPLY (VALUES
    ('Q1', Q1_Sales, Q1_Units),
    ('Q2', Q2_Sales, Q2_Units)
) AS c(Quarter, Sales, Units);

동적 UNPIVOT: 열 이름이 계속 바뀔 때

가장 어려운 시나리오는 변환해야 할 열의 이름이나 개수가 고정되어 있지 않을 때입니다. 예를 들어 매달 Sales_2025_01, Sales_2025_02 와 같이 새로운 열이 테이블에 추가되는 경우입니다. UNPIVOT의 IN 절에는 열 이름을 직접 명시해야 하므로 이런 동적인 상황에 대응할 수 없습니다.

이때의 해결책은 ‘동적 SQL(Dynamic SQL)’ 입니다.

  1. 데이터 딕셔너리나 시스템 뷰(예: INFORMATION_SCHEMA.COLUMNS)를 조회하여 UNPIVOT 대상이 될 열 목록을 동적으로 가져옵니다.

  2. 가져온 열 목록을 이용하여 UNPIVOT 쿼리 전체를 하나의 문자열 변수로 만듭니다.

  3. EXEC 또는 sp_executesql 명령어를 사용하여 이 문자열 변수를 실행합니다.

이 방법은 매우 강력하지만, 쿼리 작성이 복잡하고 SQL 인젝션과 같은 보안 위험에 주의해야 하므로 신중하게 사용해야 합니다.

5. 정리: UNPIVOT, 언제 사용해야 할까?

UNPIVOT은 데이터 전처리 및 분석 준비 단계에서 빛을 발하는 핵심적인 도구입니다.

이럴 때 UNPIVOT을 사용하세요:

  • 보고서용 데이터를 분석용으로 변환할 때: 월별, 분기별, 항목별로 넓게 펼쳐진 데이터를 합계, 평균, 추세 분석 등을 위해 긴 형태로 바꿔야 할 때 가장 먼저 떠올려야 합니다.

  • 여러 열에 분산된 데이터를 집계할 때: 여러 열에 흩어져 있는 동일한 성격의 데이터(예: 각기 다른 센서에서 수집된 값)를 하나의 열로 모아 전체 통계를 내고 싶을 때 유용합니다.

  • 데이터 정규화가 필요할 때: 데이터베이스 설계 원칙에 따라 테이블을 더 깔끔하고 관리하기 쉬운 구조로 재구성하고자 할 때 사용합니다.

UNPIVOT은 넓게 펼쳐진 데이터에 생명을 불어넣어 분석가가 원하는 대로 데이터를 자르고, 붙이고, 요리할 수 있게 만들어주는 마법과도 같습니다. 처음에는 그 구조가 낯설게 느껴질 수 있지만, 이 핸드북에서 다룬 개념과 예제를 통해 차근차근 익혀나간다면, 여러분의 데이터 분석 여정에서 가장 든든한 무기 중 하나가 될 것입니다. 이제, 여러분의 넓은 데이터를 UNPIVOT으로 멋지게 변신시켜 볼 시간입니다.

레퍼런스(References)

UNPIVOT

그래프 뷰

백링크