이 문서에서는 SQL Server에서 PIVOT 및 UNPIVOT 연산자를 사용하는 방법에 대한 전체 개요를 제공합니다. PIVOT 및 UNPIVOT 연산자는 다음을 허용하는 관계 연산자와 유사합니다. 테이블 반환 표현식을 다른 테이블로 변환 . 두 연산자 모두 대량의 데이터를 신속하게 결합하고 비교하는 데 도움이 되는 다차원 보고를 생성합니다.
우리는 피벗 연산자 테이블 반환 표현식을 변환해야 할 때. 그것은 분할 한 열의 고유 값을 여러 열로 최종 결과에서. 또한 집합체 최종 결과에 필요한 나머지 열 값. UNPIVOT 연산자 테이블 반환 식 열의 데이터를 PIVOT의 반대인 열 값으로 변환합니다.
아래의 간단한 다이어그램을 통해 이해해 보겠습니다.
이 그림의 왼쪽에서 우리는 다음을 볼 수 있습니다. 원본 데이터 세트 , 여기에는 세 개의 열이 있습니다. 연도, 지역, 그리고 매상 . 다음으로 오른쪽에 PIVOT 테이블이 있습니다. 지역(행)을 북쪽과 남쪽(열)으로 . 행을 열로 변환한 후 다음을 수행할 수 있습니다. Sales 열 값의 집계 PIVOT 테이블의 열과 행 사이의 각 교차점에 대해
먼저 다음과 같은 테이블을 만들어 보겠습니다. 피벗_데모 PIVOT 및 UNPIVOT 연산자를 시연합니다. 다음 명령문은 지정된 데이터베이스에 새 테이블을 생성합니다.
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
그런 다음 아래와 같이 이 테이블에 일부 데이터를 삽입합니다.
INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500);
SELECT 문을 사용하여 데이터를 확인할 수 있습니다. 우리는 아래와 같은 결과를 얻을 것입니다:
피벗 연산자
이 연산자는 테이블 반환 표현식을 회전하는 데 사용됩니다. SQL Server 2005 버전에서 처음 도입되었습니다. 데이터를 행에서 열로 변환합니다. 한 열의 고유 값을 여러 열로 분할한 다음 최종 결과에 필요한 나머지 열 값을 집계합니다.
PIVOT 테이블을 생성하려면 다음 단계를 따라야 합니다.
- 피버팅할 기본 데이터세트를 선택합니다.
- 파생 테이블 또는 CTE(공통 테이블 표현식)를 사용하여 임시 결과를 생성합니다.
- PIVOT 연산자를 활용하세요.
통사론
다음 구문은 SQL Server에서 PIVOT을 사용하는 방법을 보여줍니다.
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
이 스크립트를 중단하면 두 개의 별도 섹션이 있음을 알 수 있습니다. 첫 번째 섹션에서는 기본 테이블에서 데이터를 선택하고, 두 번째 섹션에서는 PIVOT 테이블 구성 방법을 결정합니다. 두 번째 부분에는 SUM, FOR 및 IN과 같은 특수 키워드도 포함되어 있습니다. PIVOT 연산자에서 이러한 키워드의 의미를 살펴보겠습니다.
합집합
이 연산자는 다음과 같은 데 사용됩니다. 값을 집계 PIVOT 테이블에 사용되는 지정된 열에서. 값 섹션에 대해 집계된 열 표시를 얻으려면 PIVOT 연산자와 함께 사용해야 합니다.
FOR 키워드
이 키워드는 PIVOT 테이블 문에 사용됩니다. PIVOT 연산자를 지시 PIVOT 함수를 적용해야 하는 열입니다. 기본적으로 행에서 열로 변환될 열 이름을 나타냅니다.
IN 키워드
이 키워드 모든 고유 값을 나열합니다. PIVOT 열에서 PIVOT 테이블의 열로 표시됩니다.
예
다양한 사례를 통해 이해해보자.
1. 다음 명령문은 먼저 피벗을 위한 기본 데이터로 Year, North 및 South 열을 선택합니다. 그런 다음 파생 테이블을 사용하여 임시 결과를 생성하고 마지막으로 PIVOT 연산자를 적용하여 최종 출력을 생성합니다. 이 출력은 또한 오름차순 연도로 정렬됩니다.
눈 대 얼음
SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year
이 문을 실행하면 아래 출력이 생성됩니다. 여기서 우리는 다음을 볼 수 있습니다. 연도 값에 해당하는 북부 및 남부 지역 매출의 계산된 합계 .
2. 이것은 지역 값에 해당하는 각 연도의 매출 합계를 계산할 또 다른 예입니다.
SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region;
이 문을 실행하면 오류가 발생하다 숫자 값을 열 이름으로 직접 지정할 수 없기 때문입니다.
그러나 SQL Server에서는 각 정수 값 앞에 대괄호를 사용하여 이 문제를 방지할 수 있습니다. 업데이트된 문은 다음 코드 조각에 표시됩니다.
SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region;
이 문은 성공적으로 실행되었으며 지역 값에 해당하는 각 연도의 계산된 매출 합계를 표시합니다.
3. PIVOT 테이블을 가져오는 이전 예는 가능한 모든 PIVOT 열 값을 알고 있을 때 유용합니다. 그러나 다가오는 해에는 열 수가 증가한다고 가정해 보겠습니다. 앞의 예를 살펴보면 2010년, 2011년, 2012년이 PIVOT 열로 있습니다. 그러나 이러한 열이 나중에 변경되지 않을 것이라는 보장은 없습니다. 2013년이나 2014년 또는 그 이상의 데이터가 있으면 어떻게 되나요? 그러한 경우에는 다음을 사용해야 합니다. 동적 피벗 테이블 이 문제를 해결하기 위한 쿼리입니다.
동적 PIVOT 테이블 쿼리는 전체 PIVOT 스크립트를 저장 프로시저에 캡슐화합니다. 이 절차에서는 조정 가능한 옵션을 제공하므로 몇 가지 매개변수화된 값을 변경하여 요구 사항을 수정할 수 있습니다.
다음 SQL 코드는 동적 PIVOT 테이블의 작동을 설명합니다. 이 스크립트에서는 먼저 PIVOT 열에서 모든 고유 값을 검색한 다음 런타임 시 PIVOT 쿼리를 실행하기 위한 SQL 문을 작성했습니다. 이 스크립트를 실행한 후의 출력을 살펴보겠습니다.
CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END
이 스크립트에서는 두 개의 매개변수화된 변수를 만들었습니다. 이에 대한 설명은 다음과 같습니다.
@PivotColumn : 이 변수는 PIVOT 테이블이 생성된 원본 테이블에서 열 이름을 가져옵니다. 예를 들어 , 여기서 '지역' 열에는 해당 열에서 사용 가능한 모든 지역이 표시됩니다.
@피벗리스트 : 이 변수는 PIVOT 테이블의 출력 열로 표시하려는 열 목록을 사용합니다.
동적 저장 프로시저 실행
자바 while 조건
동적 저장 프로시저를 성공적으로 생성한 후에는 이를 실행할 준비가 되었습니다. 다음 문은 런타임에 PIVOT 테이블을 표시하기 위해 동적 저장 프로시저를 호출하는 데 사용됩니다.
EXEC DynamicPivotTable N'Region', N'[North], [South]'
여기서는 이제 열 이름 ''을 지정했습니다. 지역 '를 첫 번째 매개변수로, PIVOT 열 목록을 두 번째 매개변수로 사용합니다. 스크립트를 실행하면 다음 출력이 표시됩니다.
이제 나중에 런타임에 더 많은 열을 추가하여 PIVOT 테이블을 표시할 수 있는데, 이는 처음 두 예제에서는 불가능합니다.
UNPIVOT 연산자
이는 SQL Server의 PIVOT 연산자와 반대되는 방법입니다. 이 운영자는 다음 작업을 수행합니다. PIVOT의 반대 동작 데이터를 열에서 행으로 변환하여 UNPIVOT 연산자는 또한 PIVOT 테이블을 일반 테이블로 회전시킵니다. SQL Server 2005 버전에서 처음 도입되었습니다.
통사론
다음 구문은 SQL Server의 UNPIVOT을 보여줍니다.
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
예
예제를 통해 PIVOT 작업을 UNPIVOT하는 방법을 이해해 보겠습니다. 우리는 먼저 원본 테이블과 PIVOT 테이블 그런 다음 이 테이블에 UNPIVOT 연산자를 적용했습니다.
다음 코드 조각은 먼저 임시 테이블 변수 @Tab을 선언합니다.
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
다음으로, 이 테이블에 아래와 같이 값을 삽입하겠습니다.
INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year
이제 아래 명령문을 사용하여 UNPIVOT 작업을 수행할 수 있습니다.
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
코드 조각을 실행하면 다음 출력이 반환됩니다.
아래 코드 조각은 단일 쿼리 내의 동일한 테이블에 대해 먼저 PIVOT 작업을 수행한 다음 UNPIVOT 작업을 수행하는 또 다른 예입니다.
SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
코드 조각을 실행하면 동일한 출력이 표시됩니다.
참고: UNPIVOT 프로세스는 PIVOT 프로시저의 반대 작업이지만 정확한 반전은 아닙니다. PIVOT이 집계를 계산할 때 행이 병합되고 결과에서 여러 행이 단일 행으로 결합되므로 UNPIVOT 작업에서는 테이블을 원본과 동일하게 만들 수 없습니다. 그러나 PIVOT 연산자가 많은 행을 단일 행으로 병합하지 않는 경우 UNPIVOT 연산자는 PIVOT 출력에서 원본 테이블을 가져올 수 있습니다.
결론
이 문서에서는 SQL Server의 PIVOT 및 UNPIVOT 연산자에 대한 전체 개요를 제공하고 테이블 식을 다른 식으로 변환합니다. UNPIVOT은 PIVOT의 역연산이지만 PIVOT 결과의 정확한 역은 아닙니다.