결과 집합을 피벗하고 피벗 해제하는 쿼리 작성

7분

SQL Server의 피벗을 사용하여 행 기반 방향에서 열 기반 방향으로 데이터가 표시되는 방식을 회전합니다. 피벗할 때 열의 값을 고유 값 목록에 통합한 다음 해당 목록을 열 머리글로 프로젝션합니다. 일반적으로 새 열의 열 값에 대한 집계가 포함됩니다.

예를 들어 아래의 부분 원본 데이터에 는 Category / Orderyear 쌍의 각 인스턴스에 대한 Qty 값과 함께 Category 및 Orderyear 의 반복 값이 나열됩니다.

카테고리	수량	Orderyear
유제품	12	2019
곡물/시리얼	10	2019
유제품	5	2019
해산물	2	2020
과자류	36	2020
조미료	35	2020
과자류	55	2020
조미료	16	2020
유제품	60 (육십)	2020
유제품	20	2020
과자류	24	2020
... (영향을 받는 행 2155개)

위의 표는 중복 값이 많은 2,000개가 넘는 행을 나타냅니다. 범주 및 연도별로 결과를 분석하려면 다음과 같이 표시할 값을 정렬하고, 그 과정에서 Qty 열을 합산하는 것이 좋습니다.

카테고리	2019	2020	2021
음료	1842	3996	3694
조미료	962	2895	1441
과자류	1357	4137	2412
유제품	2086	4374	2689
곡물/시리얼	549	2636	1377
육류/가금류	950	2189	1060
Produce	549	1583	858
해산물	1286	3679	2716
(영향을 받는 행 8개)

결과 집합은 이제 총 8개의 행입니다. 피벗 프로세스에서 각 연도는 열 머리글로 만들어졌으며 Qty 열의 값은 범주 별로 그룹화되고 집계되었습니다.

PIVOT을 사용하여 결과 집합 피벗

PIVOT 연산자를 사용하여 결과 집합을 피벗할 수 있습니다. Transact-SQL PIVOT 테이블 연산자는 SELECT 문의 FROM 절 출력에서 작동합니다. PIVOT을 사용하려면 연산자에 다음 세 가지 요소를 제공해야 합니다.

그룹화: FROM 절에서 입력 열을 제공합니다. 이러한 열에서 PIVOT은 집계를 위해 데이터를 그룹화하는 데 사용할 열을 결정합니다. 이는 PIVOT 연산자의 다른 요소로 사용되지 않는 열을 살펴보는 데 기반합니다.
분산: 피벗된 데이터의 열 머리글로 사용할 쉼표로 구분된 값 목록을 제공합니다. 값은 원본 데이터에서 발생해야 합니다.
집계: 그룹화된 행에서 수행할 집계 함수(SUM 등)를 제공합니다.

또한 PIVOT 연산자의 결과 테이블에 테이블 별칭을 할당해야 합니다. 다음 예제에서는 현재 위치에 있는 요소를 보여줍니다.

SELECT  Category, [2019],[2020],[2021]
FROM  ( SELECT  Category, Qty, Orderyear FROM Sales.CategoryQtyYear) AS D 
          PIVOT(SUM(qty) FOR orderyear IN ([2019],[2020],[2021])) AS pvt;

위의 예제에서 Orderyear 는 분산 값을 제공하는 열이고, Qty는 집계에 사용되고, 범주 는 그룹화에 사용됩니다. 순서 값 은 결과 열의 식별자임을 나타내기 위해 구분 기호로 묶입니다.

UNPIVOT를 사용하여 결과 집합 피벗 해제

피벗 해제 데이터는 피벗 데이터의 논리적 반대입니다. 피벗 해제는 행을 열로 바꾸는 대신 열을 행으로 바꿉니다. 이는 이미 피벗된 데이터를 가져와서(Transact-SQL PIVOT 연산자를 사용하거나 사용하지 않고) 행 지향 테이블 형식 디스플레이로 반환하는 데 유용한 기술입니다. UNPIVOT 테이블 연산자를 사용하여 이 작업을 수행할 수 있습니다.

UNPIVOT 연산자를 사용하려면 다음 세 가지 요소를 제공합니다.

피벗 해제할 원본 열입니다.
피벗되지 않은 값을 표시할 새 열의 이름입니다.
피벗되지 않은 값의 이름을 표시할 열의 이름입니다.

다음 예제에서는 새 열 이름 orderyear 및 새 qty 열에 표시할 qty 값을 사용하여 2019, 2020 및 2021을 피벗 해제할 열로 지정합니다.

SELECT category, qty, orderyear
FROM Sales.PivotedCategorySales
UNPIVOT(qty FOR orderyear IN([2019],[2020],[2021])) AS unpvt;

데이터 피벗 해제 시 하나 이상의 열이 행으로 변환할 원본으로 정의됩니다. 해당 열의 데이터는 피벗 해제되는 열 수에 따라 하나 이상의 새 행으로 분산되거나 분할됩니다. 다음 원본 데이터에서는 세 개의 열이 피벗 해제됩니다. 각 Orderyear 값은 새 행으로 복사되고 해당 범주 값과 연결됩니다. 프로세스에서 NULL이 제거되고 행이 만들어지지 않습니다.

카테고리	2019	2020	2021
음료	1842	3996	3694
조미료	962	2895	1441
과자류	1357	4137	2412
유제품	2086	374	2689
곡물/시리얼	549	2636	1377
육류/가금류	950	2189	1060
Produce	549	1583	858
해산물	1286	3679	2716

범주와 Orderyear의 각 교집합에 대해 다음 부분 결과와 같이 새 행이 만들어집니다.

카테고리	수량	orderyear
음료	1842	2019
음료	3996	2020
음료	3694	2021
조미료	962	2019
조미료	2895	2020
조미료	1441	2021
과자류	1357	2019
과자류	4137	2020
과자류	2412	2021

피벗 해제는 원래 데이터를 복원하지 않습니다. 원래 피벗의 집계 프로세스 중에 세부 수준 데이터가 손실되었습니다. UNPIVOT에는 원래 세부 정보 값으로 반환할 값을 할당할 수 없습니다.

피드백

이 페이지가 도움이 되었나요?