MSSQL 피벗, 언피벗 PIVOT, UNPIVOT

데이터베이스 세계에서 가장 일반적인 작업 중 하나는 데이터를 한 구조에서 다른 구조로 변환하는 것입니다. 이러한 변환은 보고 및 데이터 분석과 관련하여 매우 중요합니다. 마이크로소프트 SQL 서버(MSSQL)는 이를 달성하기 위한 강력한 도구를 제공하며, 가장 핵심적인 두 가지 작업은 피벗(PIVOT)과 언피벗(UNPIVOT)입니다. 이러한 작업을 통해 데이터를 회전하고 재구성할 수 있으므로 분석 목적에 더욱 적합합니다.

이 블로그에서는 MSSQL PIVOT 및 UNPIVOT에 대해 자세히 살펴보고, MSSQL PIVOT가 무엇인지, 사용 방법 및 실제 시나리오가 매우 가치 있는 시나리오에 대해 알아보겠습니다.

PIVOT 

 

피벗은 행 수준의 데이터를 컬럼 형식으로 변환할 수 있는 연산으로 특히 교차표 작성 및 요약 보고서 작성에 유용합니다. 피벗의 핵심 개념은 데이터를 집계하여 보다 구조화된 형태로 표시하는 것입니다.

 

SELECT *
FROM (
   SELECT <columns_to_select>, <columns_to_pivot>, <column_to_aggregate>
   FROM <your_table>
) AS SourceTable
PIVOT (
   <aggregate_function>(<column_to_aggregate>)
   FOR <columns_to_pivot> IN (<pivot_column1>, <pivot_column2>, ...)
) AS PivotTable;

 

 

# Parameters
- '<columns_to_select>': 결과에 포함할 비선택 열입니다.
- '<columns_to_pivot>': 값이 피벗 결과의 새 열이 될 열입니다.
- '<column_to_aggregate>': 값을 집계할 열입니다.
- '<aggregate_function >': 집성된 열에 적용할 집성 함수(예: SUM, AVG, MAX).
- '<pivot_column1>, <pivot_column2>, ...': 결과에서 새 컬럼 헤더가 될 '<columns_to_pivot>'의 값입니다.

# Example

"ProductID", "Month" 및 "Revenue" 열이 있는 테이블 "SalesData"가 있다고 가정합니다. 피벗을 사용하여 이 데이터를 보다 구조화된 보고서로 변환할 수 있습니다. 여기서 각 월은 별도의 열이 되며 각 제품별로 수익이 집계됩니다.

 

SELECT *
FROM (
   SELECT ProductID, Month, Revenue
   FROM SalesData
) AS SourceTable
PIVOT (
   SUM(Revenue)
   FOR Month IN ([January], [February], [March])
) AS PivotTable

 

MSSQL PIVOT

UNPIVOT

반면에 UNPIVOT는 PIVOT의 역으로, 기둥 데이터를 행 수준의 데이터로 다시 변환하는 데 사용되며, 이는 세밀한 분석과 시각화에 종종 필요합니다. UNPIVOT는 정규화된 형태의 데이터로 작업하기 쉽도록 합니다.

SELECT <columns_to_select>, <value_column>
FROM (
   SELECT <columns_to_select>, <columns_to_unpivot>
   FROM <your_table>
) AS SourceTable
UNPIVOT (
   <value_column> FOR <column_to_unpivot> IN (<unpivot_column1>, <unpivot_column2>, ...)
) AS UnpivotTable;

 

# Parameters
- '<columns_to_select>': 결과에 포함할 비선택 열입니다.
- '<value_column>': 무투표 값이 저장될 열입니다.
- '<columns_to_unpivot>': 값이 피벗되지 않는 열입니다.
- '<column_to_unpivot>': 무투표 열 헤더를 저장할 새 열입니다.
- '<unpivot_column1>, <unpivot_column2>, ...': 피벗을 해제할 '<columns_to_unpivot>'의 열입니다.

# Example

동일한 "판매 데이터" 표를 사용하여 이전에 피벗된 데이터를 원래 형태로 다시 변환하여 개별 제품 분석을 보다 쉽게 수행할 수 있도록 하고 싶다고 가정합니다.

SELECT ProductID, [Month], Revenue
FROM (
   SELECT ProductID, [January], [February], [March]
   FROM PivotTable
) AS SourceTable
UNPIVOT (
   Revenue FOR [Month] IN ([January], [February], [March])
) AS UnpivotTable;

 

SQL UNIPVOT

이제 PIVOT와 UNPIVOT가 매우 유용하게 사용될 수 있는 몇 가지 시나리오를 살펴보겠습니다:

1. **판매 데이터 분석**: 

피봇은 다양한 제품의 월별 판매 보고서를 작성하는 데 도움을 주고, 유피봇은 이 데이터를 되돌려서 보다 상세한 제품 성능 분석을 할 수 있습니다.

2. **설문자료**: 

PIVOT을 사용하여 다양한 질문에 대한 설문조사 결과를 요약할 수 있으며, UNPIVOT은 이 데이터를 다시 개별 응답으로 변환할 수 있습니다.

3. **재무보고서**: 

피봇은 월별 요약재무제표 작성에 도움을 줄 수 있으며, 유피봇은 상세한 거래수준 분석을 위해 이 데이터를 되돌릴 수 있습니다.

4. **Inventory Tracking**: 

PIVOT는 재고 수준을 높게 볼 수 있으며, UNPIVOT는 시간에 따른 재고 변화를 분석하는 데 도움을 줄 수 있습니다.

5. **직원 교대 스케쥴링**: 

피봇은 직원별로 일정을 만들 수 있으며, UNPIVOT는 교대 및 중복되는 일정을 파악하는데 사용될 수 있습니다.

결론적으로 MSSQL 피벗 및 UNPIVOT 작업을 이해하고 마스터링하는 것은 SQL Server에서 데이터를 효율적으로 관리 및 분석하기 위해 매우 중요합니다. 이러한 작업을 통해 데이터를 한 형식에서 다른 형식으로 변환할 수 있으므로 보다 통찰력 있는 분석 및 보고가 가능합니다. 재무 데이터, 설문 조사 응답 또는 기타 데이터셋을 처리하는 경우에 상관없이 피벗 및 UNPIVOT는 SQL 아스널에서 데이터를 변환하는 데 유용한 툴입니다.

 

---

 

(Eng. ver)

In the world of databases, one of the most common operations is transforming data from one structure to another. This transformation is crucial when it comes to reporting and data analysis. Microsoft SQL Server (MSSQL) provides powerful tools to achieve this, and two of the most essential operations are PIVOT and UNPIVOT. These operations allow you to rotate and reshape your data, making it more suitable for analytical purposes.

In this blog, we'll delve into MSSQL PIVOT and UNPIVOT, exploring what they are, how to use them, and real-world scenarios where they can be incredibly valuable.

# Understanding PIVOT

PIVOT is an operation that allows you to convert row-level data into a columnar format, which is particularly useful for creating cross-tabulations and summary reports. The key concept behind PIVOT is to aggregate data and display it in a more structured form.

# Syntax

SELECT *
FROM (
   SELECT <columns_to_select>, <columns_to_pivot>, <column_to_aggregate>
   FROM <your_table>
) AS SourceTable
PIVOT (
   <aggregate_function>(<column_to_aggregate>)
   FOR <columns_to_pivot> IN (<pivot_column1>, <pivot_column2>, ...)
) AS PivotTable;

 

# Parameters
- `<columns_to_select>`: The non-pivoted columns you want to include in your result.
- `<columns_to_pivot>`: The columns whose values will become the new columns in the pivot result.
- `<column_to_aggregate>`: The column to aggregate values from.
- `<aggregate_function>`: The aggregation function (e.g., SUM, AVG, MAX) to apply to the aggregated column.
- `<pivot_column1>, <pivot_column2>, ...`: The values from the `<columns_to_pivot>` that will become the new column headers in the result.

# Example
Suppose you have a table "SalesData" with columns "ProductID," "Month," and "Revenue." You can use PIVOT to transform this data into a more structured report, where each month becomes a separate column, and the revenue is aggregated for each product.

SELECT *
FROM (
   SELECT ProductID, Month, Revenue
   FROM SalesData
) AS SourceTable
PIVOT (
   SUM(Revenue)
   FOR Month IN ([January], [February], [March])
) AS PivotTable;

 The Power of UNPIVOT

On the other hand, UNPIVOT is the reverse of PIVOT. It is used to transform columnar data back into row-level data, which is often necessary for detailed analysis and visualization. UNPIVOT makes it easy to work with data in its normalized form.

#Syntax:

SELECT *
FROM (
   SELECT ProductID, Month, Revenue
   FROM SalesData
) AS SourceTable
PIVOT (
   SUM(Revenue)
   FOR Month IN ([January], [February], [March])
) AS PivotTable;

# Parameters
- `<columns_to_select>`: The non-unpivoted columns you want to include in your result.
- `<value_column>`: The column where the unpivoted values will be stored.
- `<columns_to_unpivot>`: The columns whose values will be unpivoted.
- `<column_to_unpivot>`: The new column that will store the unpivoted column headers.
- `<unpivot_column1>, <unpivot_column2>, ...`: The columns from the `<columns_to_unpivot>` that you want to unpivot.

# Example
Using the same "SalesData" table, let's say you want to convert the previously pivoted data back to its original form, making it easier to perform individual product analysis.

SELECT ProductID, [Month], Revenue
FROM (
   SELECT ProductID, [January], [February], [March]
   FROM PivotTable
) AS SourceTable
UNPIVOT (
   Revenue FOR [Month] IN ([January], [February], [March])
) AS UnpivotTable;

 

SQL UNPIVOT

# Real-World Scenarios

Now, let's explore some real-world scenarios where PIVOT and UNPIVOT can be incredibly useful:

1. **Sales Data Analysis**: PIVOT can help create monthly sales reports for various products, while UNPIVOT can revert this data for more detailed product performance analysis.

2. **Survey Data**: PIVOT can be used to summarize survey results for different questions, and UNPIVOT can transform this data back into individual responses.

3. **Financial Reports**: PIVOT can help create summarized financial statements by month, and UNPIVOT can revert this data for detailed transaction-level analysis.

4. **Inventory Tracking**: PIVOT can provide a high-level view of inventory levels, and UNPIVOT can help in analyzing stock changes over time.

5. **Employee Shift Scheduling**: PIVOT can create schedules for each employee, and UNPIVOT can be used to identify shifts and overlapping schedules.

In conclusion, understanding and mastering the MSSQL PIVOT and UNPIVOT operations is crucial for efficiently managing and analyzing data in SQL Server. These operations enable you to transform data from one format to another, allowing for more insightful analysis and reporting. Whether you're dealing with financial data, survey responses, or any other dataset, PIVOT and UNPIVOT are valuable tools in your SQL arsenal for data transformation.