この記事では、SQL PivotおよびSQL Unpivot演算子と、それらがSQL Serverデータを転置する また、PIVOT IN句ですべての列値を指定することにより、集計された個別の値を結果セット内の列として変換するために使用できるPIVOTおよびUNPIVOT関係演算子を使用する静的および動的の両方の方法についても説明します。
SQL Server pivot Introduction
データセットを行から列に、列から行に転置するには、いくつかの方法があります。 SQL Pivotは、行から列への転置を可能にし、途中で可能な集計を実行する技術の1つです。 SQL PIVOTおよびSQL UNPIVOT関係演算子は、テーブル値の二次元データを別の形式のデータに転置します。 SQL PIVOTは、1つの列の一意の値セットから出力内の複数の列にテーブル値式を転置し、集計を実行します。 SQL UNPIVOTは、テーブル値式の列を列値に変換することによって、SQL PIVOTの逆の操作を実行します。
ほとんどの場合、静的ピボットテクニックはビジネス要件で十分です。 たとえば、月次売上予測、年間売上分割、四半期売上集計などで、IN句の列は静的なままです。 他のいくつかの例では、詳細な詳細が必要であり、テーブル値式は本質的に動的であり、式の新しいセットがテーブルに含まれるすべての時間は、動的ピボッ
注：上記の描写では、ピボットの過程で、列の値が垂直から水平に回転し、ピボット解除は水平から垂直に回転
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 clause>;
SQLピボットの概要h2>
まず、データのピボットとピボット解除から始めます。 これで、SQL ServerのPIVOT演算子とUNPIVOT演算子が理解できました。 簡単に言えば、それはピボットとして知られている独自の列に異なる行の値を変換するプロセスを記述するためのちょうど別の方法です。 そして、列を行に変換するプロセスは、非ピボットとして知られています。
例1: SQLピボット操作を実行する方法
Adventureworks2014データベースから派生したサンプルデータセットを図に示します。 この場合、1つの定量的な値TotalSalesに沿ってSalesYear列を持つ単純なデータセットが得られました。 p>
これで、データセットはピボットの準備が整いました。 最初の列にSalesYearがあり、2番目の列にTotalSalesがあります。 データを転置するように要求された場合は、列内のすべての別個の年を取る必要があり、これらの列はピボット列のヘッダーになります。 したがって、2011は最初の列になり、2012は独自の列になります。 次の列Totalsalesからの実際の値は、ピボット列ヘッダーに固執します。 さて、ピボットテーブルは以下のようになります。/div>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
OrderDate)SalesYearとして、
SOH。売上からtotalsales
として小計。SalesOrderHeader SOH
売上に参加します。SohのSalesOrderDetail SOD。SalesOrderId=SOD.SalesOrderId
)As Sales
PIVOT(SUM(TotalSales)
FOR SalesYear IN(,,,))
as PVT
|
次の画像は、入力データ、ピボット構文、SQLピボット、および出力結果を示しています。p>
例2: SQL Unpivot操作を実行する方法
ここで、水平から垂直に行くのと同じくらい簡単な、他の方向、ピボット解除を見てみましょう。 この場合、選択されたすべての個別の列を取得し、それらの列ヘッダーを独自の行に変換します。 ピボットは、垂直から水平に回転するようなものです。 そして、非ピボットは、水平から垂直に回転するようなものです。/div>
|
1
2
3
4
10
11
12
13
14
15
16
17
18
19
11
12
13
14
15
16
17
18
19
19
19
19
19
19
19
19
19
|
salesyearを選択し、
totalsales
から
(
select*
から
(
select year(soh.OrderDate)SalesYearとして、
SOH。売上からtotalsales
として小計。SalesOrderHeader SOH
売上に参加します。SohのSalesOrderDetail SOD。SalesOrderId=SOD.
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
、
|
次の例では、ピボットされたデータセットを使用して値をピボット解除します。 ここで重要なのは、中間ステップとしてピボットテーブルを作成し、unpivot操作を使用して変換を行うことができたため、変換を行うのが簡単だったことです。
注：SQLピボット操作は集計された結果を列に転置しますが、SQL unpivotは列を分離された値に転置する正確な逆転ではありません行の。 Unpivot演算子は集計結果を分割しません。
例3: SQLピボット操作を使用して四半期売上高を取得する方法
ピボットの概念をよりよく理解するために、いくつかの例を見てみましょう。 このケースでは、四半期売上に基づいて四半期売上データを集計する方法について説明します。
ソリューションにジャンプする前に、ピボット操作のデータセットの作成に取り組むことを常にお勧めします。
次の例では、売上年間の集計された月次売上スプレッドを示しています。/div>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
////////////////////////////////////////Orderdate)をSalesMonthとして、
SUM(SOH.SubTotal)as TotalSales
売上から。SalesOrderHeader SOH
売上に参加します。SohのSalesOrderDetail SOD。SalesOrderId=SOD.SalesOrderId
年別グループ(SOH.
月(SOH.OrderDate)
年による順序(SOH.
月(SOH.次の出力は、ベースデータを示しています
あなたが準備ができたら、
基本データでは、ピボット演算子を適用できます。 次の例では、IN句は、DATEPART値に基づいて生成された数値以外の文字を受け入れます。 DATEPART値は’Q’と連結され、値はPIVOT演算子のIN句に供給されます。/div>
|
|
|
salesyearを選択し、
isnull(,0)をq1として、
isnull(,0)をq2として、
isnull(,0)をq3として、
isnull(,0)をq4として、
(isnull(,0)+isnull())をq4として、
(isnull(,0)+isnull())をq4として、
(isnull(,0)+isnull())をq4として、
(isnull(,0)+isnull())をq4として、
(isnull(,0)+isnull())をq4として, 0)+ISNULL(,0)+ISNULL(,0))SalesYTD
FROM
(
SELECT YEAR(SOH.OrderDate)をSalesYearとして、
CAST(‘Q’+CAST(DATEPART(QUARTER,SOH.OrderDate)AS VARCHAR(1))AS VARCHAR(2))Quarters,
SOH.売上からtotalsales
として小計。SalesOrderHeader SOH
売上に参加します。SohのSalesOrderDetail SOD。SalesOrderId=SOD.データピボットとしてのSalesOrderId(SUM(TotalSales)For Quarters IN(,,
,
,
))as pvt
Order BY SalesYear; 次の出力は、販売データの四半期分割です
例4：どのようにsqlピボット操作を使用して毎月の売上を取得する
毎月に基づいて売上分割データを表示する別の例を見てみましょう。 次の例では、IN句の値はDATEPART関数の一部として生成されます。 DATEPART値は、PIVOT演算子のIN句に供給されます。/div>
1
2
3
4
10
11
12
13
14
15
16
17
18
19
11
12
13
14
15
16
17
18
19
19
19
19
19
19
19
19
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
39
39
39
39
39
39
39
|
SalesYearを選択し、
Isnull(,0)をJanとして、
Isnull(,0)をFebとして、
Isnull(,0)をMarとして、
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)をdecとして、
(isnull(,0)+isnull(,0)をdecとして、
(isnull(,0)+isnull(,0))をdecとして、
(isnull(,0)+isnull(,0))をdecとして、
(isnull(,0)+isnull(,0)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)+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))SalesYTD
から
(
SELECT YEAR(SOH.OrderDate)をSalesYearとして、
DATEPART(MONTH,SOH.
div売上からtotalsales
として小計。SalesOrderHeader SOH
売上に参加します。SohのSalesOrderDetail SOD。SalesOrderId=SOD.データピボットとして(SUM(TotalSales)For Months IN(,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
))pvtとして;
|
次の出力は、売上データの毎月の分割です
例5:動的SQLピボット操作を使用して毎月の売上を取得する方法
私たちは、動的SQLピボット操作を使用して売上データの毎月の分割に対処しましょうピボット これまでのところ、静的ピボット操作について説明しました。 静的なピボット操作を動的に変換するには、IN句からハードコードされた値を削除する必要があります。 まず、データセットからdistinct式を取得し、すべての式を連結して文字列を準備します。 次の例では、@columnnameを使用してすべての式を連結します。 連結された式は、pivot IN句に供給されます。 残りは、静的SQLを動的SQLに単純に変換し、sp_executesqlストアドプロシージャを使用して@DMLを呼び出します。/div>
|
1
2
3
4
10
11
12
13
14
15
16
17
18
19
11
12
13
14
15
16
17
18
19
19
19
19
19
19
19
19
19
/div>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
@ColumnName AS NVARCHAR(MAX)
SELECT@ColumnName=ISNULL(@ColumnName+’,’,”)
+QUOTENAME(Months)
FROM(SELECT DISTINCT DATEPART(MONTH,SOH.月
販売から。SalesOrderHeader SOH
売上に参加します。SohのSalesOrderDetail SOD。SalesOrderId=SOD.SalesOrderId
年別グループ(SOH.日付を指定するには、
を使用します。OrderDate))As Months
-動的を使用してピボットクエリを準備します
SET@dml=
N’Select SalesYear,’+@ColumnName+’FROM
(
SELECT YEAR(SOH.OrderDate)をSalesYearとして、
DATEPART(MONTH,SOH.
divSubTotal)as TotalSales
売上から。SalesOrderHeader SOH
売上に参加します。SohのSalesOrderDetail SOD。SalesOrderId=SOD.SalesOrderId
年別グループ(SOH.日付を指定するには、
を使用します。OrderDate))AS T
PIVOT(SUM(TotalSales)
For Months IN(‘+@ColumnName+’))AS PVTTable’
–Print@DynamicPivotQuery
–動的ピボットクエリを実行
EXEC sp_executesql@dml
|
|
それは今のところすべてです…
ラップアップ
これまで、SQL pivotとsql unpivotの基本的な概念といくつかの例を
このサンプルは、SQL Server adventureworks2014データベースに基づいています。 試してみて、すべてのサンプルを自分でテストすることができます。 あなたが何か質問があれば、以下にコメントすること自由に感じてください…
私はデータベース技術に関する11年以上の豊富な実践的な経験。 私はマイクロソフト認定プロフェッショナルであり、コンピュータアプリケーションのマスターの学位を取得しています。
私の専門は、高可用性ソリューションとクロスプラットフォームDB移行を実装する&の設計にあります。 現在取り組んでいる技術は、SQL Server、PowerShell、Oracle、MongoDBです。
Prashanth Jayaramによるすべての投稿を表示
Prashanth Jayaramによる最新の投稿（すべてを参照）
- SQLのデータベース監査の概要-January28,2021
- Azure間のAzureデータ同期sqlデータベースとオンプレミスsql Server-2021年1月20日
- powershellを使用してazure sqlデータベースのインポート/エクスポート操作を実行する方法-2021年1月14日
|
|
|
|
