이 문서에서 자세히 알아보도록 하겠습니다-SQL 피벗 및 SQL 피벗 해제 운영자와 어떻게 그들이 유용할 수 있습을 바꾸어 SQL 서버 데이터입니다. 또한,우리는 토론할 것이 모두 정적 및 동적 방법을 사용하여 피벗고 피벗 해제 관계형 연산자를 사용할 수 있는 변환하는 집계된 고유한 값으로 열(s)에 결과를 지정하여 설정 모두에서 열 값을 피벗에 절이 있습니다.
SQL Server 피벗 소개
데이터 세트를 행에서 열로,열에서 행으로 전치하는 몇 가지 방법이 있습니다. SQL 피벗은 열에 행의 전치를 허용하고 길을 따라 가능한 집계를 수행하는 기술 중 하나입니다. SQL PIVOT 및 SQL UNPIVOT 관계형 연산자는 테이블 값 2 차원 데이터를 다른 형태의 데이터로 바꿉니다. SQL PIVOT 은 한 열의 고유 한 값 집합에서 출력의 여러 열로 테이블 값 표현식을 전달하고 집계를 수행합니다. SQL UNPIVOT 은 테이블 값 표현식의 열을 열 값으로 변환하여 SQL 피벗의 반대 연산을 수행합니다.
대부분의 경우 정적 피벗 기술은 비즈니스 요구 사항으로 충분합니다. 예를 들어,월 판매 예측,연간 판매로 분할,분기별 판매 집계,등등 어디에서 절로 열 정적 남아 있습니다. 다른 경우에,우리에게 필요한 세부 정보 및 테이블 표현서 더욱 역동적인 성격 그리고 모든 시간을 새로운 설정의 표현에 포함되어 있는 테이블 다음 동적 피벗 것 최고의 선택입니다.
참고:위의 묘사를 볼 수 있습하는 과정에서의 피봇의 열 값은 회전에서 수직,수평 및 피벗 해제는 다음과 같 회전에서 수평 수 있습니다.
Syntax
SELECT <non-pivoted column>,
AS <column alias>,
AS <column alias>,
…
AS <column alias>
FROM
(
<SELECT QUERY>)
AS <Alias for temporary data set>
PIVOT
(
<Aggregate function>( Aggregate column)
FOR
IN ( , ,
… )
) AS <>
<ORDER BY 절>;
시작하기 SQL 피벗
우리가 시작으로 선회 및 un-선회는 데이터입니다. 지금까지 PIVOT 및 UNPIVOT 연산자가 SQL Server 에 무엇인지 이해합니다. 간단히 말해서,그것은 단지 다른 방법을 설명하는 과정을 뚜렷한 행 값으로 자신의 열을 알려져 있으로 회전. 그리고 열을 행으로 바꾸는 과정은 선회하지 않는 것으로 알려져 있습니다.
실시예 1: SQL 피벗 작업을 수행하는 방법
AdventureWorks2014 데이터베이스에서 파생 된 샘플 데이터 세트를 그림으로 그려 보겠습니다. 이 경우 하나의 양적 가치 TotalSales 를 따라 SalesYear 열이있는 간단한 데이터 세트를 얻었습니다. 이제 데이터 세트가 피벗 할 준비가되었습니다. 첫 번째 열에 SalesYear 가 있고 두 번째 열에 TotalSales 가 있습니다. 데이터를 전치하도록 요청한 경우 열의 모든 고유 연도를 취해야하며 이러한 열은 피벗 열의 헤더가됩니다. 따라서 2011 년은 첫 번째 열이되고 2012 년은 자체 열이됩니다. 다음 열 전체의 실제 값판매,그들은 피벗 열 머리글을 고수합니다. 이제 피벗 테이블은 아래와 같습니다.
|
1
2
3
4
5
6
7
8
9
10
|
SELECT*FROM
(
선택해(SOH.나는 이것이 내가 할 수있는 유일한 방법이라고 생각한다.Sales 에서 TotalSales
로 소계.SalesOrderHeader SOH
판매에 참여하십시오.SalesOrderDetail SOD ON SOH.SalesOrderId=SOD.SalesOrderId
)으로 판매
피벗(SUM(TotalSales)
에 대한 SalesYear 서(,,,))
로 PVT
|
다음과 같은 이미지를 묘사하는 입력한 데이터,피벗 구문 SQL 피벗고,출력 결과입니다.
실시예 2: SQL Unpivot 작업을 수행하는 방법
이제 수평에서 수직으로가는 것만 큼 간단한 다른 방향으로 un-pivoting 을 살펴 보겠습니다. 이 경우 선택한 별개의 열을 모두 가져 와서 해당 열 머리글을 자신의 행으로 바꿉니다. 피벗은 수직에서 수평으로 회전하는 것과 같습니다. 그리고 선회 해제는 수평에서 수직으로 회전하는 것과 같습니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
선택 SalesYear,
TotalSales
에서는
(
선택*
에서는
(
선택해(SOH.나는 이것이 내가 할 수있는 유일한 방법이라고 생각한다.Sales 에서 TotalSales
로 소계.SalesOrderHeader SOH
판매에 참여하십시오.SalesOrderDetail SOD ON SOH.SalesOrderId=SOD.SalesOrderId
)판매로 피벗(SUM(TotalSales)에 대한 SalesYear 서(,
,
,
))로 PVT
)T 피벗 해제(TotalSales 에 대한 SalesYear 서(,
,
,
))로 upvt;
|
다음 예에서,피벗의 데이터 집합을 사용하여 un-피벗의 값이 있습니다. 주요 작품은 여기에는 이것에서 쉽게 점점 변모하기 때문에 우리를 만들 수 있었다 pivot table 중간 단계로서 다음을 수행합 변환을 사용하여 피벗 해제 작업입니다.
참고:SQL 피벗에서 작업 결과로 조옮김을 하려는 집계된 결과로 열지만 반면 SQL 피벗 해제하지 않은 정확한 반전의로 조옮김을 하려는 열에 분리된 값의 행이 있습니다. Unpivot 연산자는 집계된 결과를 분할하지 않습니다.
실시예 3: Sql 피벗 작업을 사용하여 분기 별 매출을 얻는 방법
피벗 개념을 더 잘 이해하기 위해 몇 가지 예제를 더 살펴 보겠습니다. 이 경우 분기 별 매출을 기준으로 집계 된 분기 별 판매 데이터를 얻는 방법을 살펴 보겠습니다. 솔루션에 뛰어 들기 전에 항상 피벗 작업에 대한 데이터 세트를 만드는 작업을하는 것이 좋습니다.
다음 예는 판매 연도에 걸쳐 집계 된 월별 판매 스프레드를 제공합니다.
|
1
2
3
4
5
6
7
8
9
|
선택해(SOH.이 두 가지 방법은 다음과 같습니다.이 경우 두 가지 방법이 있습니다.소계)는 판매에서 TotalSales
로 사용됩니다.SalesOrderHeader SOH
판매에 참여하십시오.SalesOrderDetail SOD ON SOH.SalesOrderId=SOD.SalesOrderId
연도 별 그룹(SOH.이 경우 두 번째 행은 두 번째 행과 두 번째 행과 두 번째 행과 두 번째 행과 두 번째 행입니다.주문 날짜)
연도 별 주문(SOH.이 경우 두 번째 행은 두 번째 행과 두 번째 행과 두 번째 행과 두 번째 행과 두 번째 행입니다.주문일);
|
다음이 나타나는 것을 볼 수 있습니다 데이터베이스
일단 당신이 준비와 함께 데이터베이스, 에 적용할 수 있는 피벗 운영자입니다. 다음 예에서 IN 절은 DATEPART 값을 기반으로 생성 된 비 숫자 문자를 허용합니다. DATEPART 값은’Q’와 연결 한 다음 값이 피벗 연산자의 IN 절에 공급됩니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
선택 SalesYear,
ISNULL(,0)로 Q1
ISNULL(,0)로 Q2,
ISNULL(,0)로 3 분기
ISNULL(,0)으로 4 분기
(ISNULL(,0)+ISNULL(, 0)+ISNULL(,0)+ISNULL(,0))SalesYTD
FROM
(
SELECT YEAR(SOH.예를 들어,CAST(‘Q’+CAST(DATEPART(QUARTER,SOH.)는 SalesYear,
CAST(‘Q’+CAST(DATEPART(QUARTER,SOH.나는 이것이 내가 할 수있는 유일한 방법이라고 생각한다.Sales 에서 TotalSales
로 소계.SalesOrderHeader SOH
판매에 참여하십시오.SalesOrderDetail SOD ON SOH.SalesOrderId=SOD.SalesOrderId
)AS Data PIVOT(SUM(TotalSales)IN Quarters IN(,
,
))AS pvt
ORDER BY SalesYear;
|
출력이 다음과 같은 분기별 분할의 판매 데이터
를 들어 4: 을 얻는 방법 월간 판매를 사용하여 SQL 피벗 운영
우리가 살펴으로 또 다른 예는 판매를 보고 분리를 기반으로 데이터에 매달합니다. 다음 예에서 in 절 값은 DATEPART 함수의 일부로 생성됩니다. DATEPART 값은 피벗 연산자의 in 절에 공급됩니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
선택 SalesYear,
Isnull(,0)로 Jan,
Isnull(,0)로 Feb,
Isnull(,0)로 마르
ISNULL(,0)으로 Apr
isnull(,0)수 있으므로,
isnull(,0)로 Jun,
isnull(,0)로 Jul,
isnull(,0)로 Aug,
isnull(,0)로 Sep,
ISNULL(,0)로 Oct
isnull(,0)로 Nov,
isnull(, 0)로 Dec,
(isnull(,0)+isnull(,0)+ISNULL(,0)+isnull(,0)+isnull(,0)+isnull(,0)+ISNULL(,0)+ISNULL(,0)+isnull(,0)+isnull(,0)+isnull(,0)+isnull(,0)+isnull(,0)+ 이 작업을 수행하려면 다음 작업을 수행해야 할 수도 있습니다.이 작업을 수행하려면 다음 작업을 수행해야합니다.OrderDate)개월,
SOH.Sales 에서 TotalSales
로 소계.SalesOrderHeader SOH
판매에 참여하십시오.SalesOrderDetail SOD ON SOH.SalesOrderId=SOD.SalesOrderId
)으로 데이터를 피벗(SUM(TotalSales)한 달에(,
,
,
,
,
,
,
,
,
,
,
))로 pvt;
|
는 다음과 같은 출력 월간 분할의 판매 데이터
예 5: 을 얻는 방법 월간 판매를 사용하여 동적 SQL 피벗 운영
우리 주소는 월의 판매를 사용하여 데이터를 동적 회전. 지금까지 정적 피벗 작업에 대해 논의했습니다. 정적 피벗 작업을 동적으로 변환하려면 IN 절에서 하드 코딩 된 값을 제거해야합니다. 먼저 데이터 세트에서 고유 한 표현식을 가져온 다음 모든 표현식을 연결하여 문자열을 준비합니다. 다음 예에서@columnname 은 모든 표현식을 연결하는 데 사용됩니다. 연결된 표현식은 절에서 피벗에 공급됩니다. 나머지는 정적 SQL 을 동적 SQL 로 간단하게 변환하고 sp_executesql 저장 프로 시저를 사용하여@DML 을 호출합니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
알리는@dml 로 이며(MAX)
선언 @열 이름으로 이며(MAX)
선택@열 이름=ISNULL(@열 이름+’,’,”)
+로써 발생(개월)
부터(선택 뚜렷한 부분(년,월,SOH.판매에서 Orderdate)개월
.SalesOrderHeader SOH
판매에 참여하십시오.SalesOrderDetail SOD ON SOH.SalesOrderId=SOD.SalesOrderId
연도 별 그룹(SOH.이 경우 두 번째 행은 두 번째 행에서 두 번째 행으로 나뉩니다.OrderDate))As Months
–dynamic
SET@dml=
N’SELECT SalesYear,’+@ColumnName+’FROM
(
SELECT YEAR(SOH.이 작업을 수행하려면 다음 작업을 수행해야합니다.이 경우 두 번째 행은 두 번째 행입니다.소계)는 판매에서 TotalSales
로 사용됩니다.SalesOrderHeader SOH
판매에 참여하십시오.SalesOrderDetail SOD ON SOH.SalesOrderId=SOD.SalesOrderId
연도 별 그룹(SOH.이 경우 두 번째 행은 두 번째 행에서 두 번째 행으로 나뉩니다.주문일))T
피벗(SUM(TotalSales)
개월 동안(‘+@열 이름+’)) 로 PVTTable’
–Print@DynamicPivotQuery
–실행하여 동적 피벗 쿼리
EXEC 전까@dml
|
는 지금…
포장
지금까지,우리는 설명 기본적인 개념과 여러 가지 예를 SQL 피벗 및 SQL 피벗 해제.
샘플은 SQL Server adventureworks2014 데이터베이스를 기반으로합니다. 당신은 시도를주고 모든 샘플을 직접 테스트 할 수 있습니다. 는 경우에 당신이 어떤 질문이 견 주시기 바랍니다.
- 저자
- 최근 게시글
내 전문 분야는 고 가용성 솔루션 및 크로스 플랫폼 DB 마이그레이션을 구현하는&설계에 있습니다. 현재 작업중인 기술은 SQL Server,PowerShell,Oracle 및 MongoDB 입니다.
모두 보기 게시물에 의해 Prashanth 자야람 박사
- 빠른 개요 데이터베이스의 감사에서 SQL-January28, 2021
- 설정하는 방법 Azure 데이터 동기화를 사 Azure SQL 데이터베이스에서 구내 SQL Server-January20,2021
- 를 수행하는 방법 Azure SQL 데이터베이스에 가져오기/내보내기를 사용하여 작업 PowerShell-January14,2021