logo

TechCodeview

SQLサーバーピボット

この記事では、SQL Server での PIVOT 演算子と UNPIVOT 演算子の使用の完全な概要を説明します。 PIVOT 演算子と UNPIVOT 演算子は、以下を可能にする関係演算子に似ています。 テーブル値式を別のテーブルに変換する 。どちらのオペレーターも、大量のデータを迅速に結合して比較するのに役立つ多次元レポートを生成します。

使用できます ピボット演算子 テーブル値式を変換する必要がある場合。それは、 1 つの列から複数の列への一意の値 最終結果では。また 骨材 最終結果に必要な残りの列の値。 UNPIVOT 演算子 テーブル値式の列のデータを列値に変換します。これは PIVOT の逆です。

開発者モードを終了する方法

以下の簡単な図を使ってそれを理解してみましょう。

SQLサーバーピボット

この図の左側には、 元のデータセット 、次の 3 つの列があります。 年、地域、 そして 販売 。次に、右側にピボット テーブルが表示されます。これは、 地域 (行) を北と南 (列) に分割 。行を列に変換した後、次のことを行うことができます。 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サーバーピボット

ピボットオペレーター

この演算子は、テーブル値式を回転するために使用されます。これは SQL Server 2005 バージョンで初めて導入されました。データを行から列に変換します。 1 つの列の一意の値を多くの列に分割し、最終結果に必要な残りの列値を集計します。

PIVOT テーブルを作成するには、次の手順に従う必要があります。

  • ピボットするベース データセットを選択します。
  • 派生テーブルまたは CTE (共通テーブル式) を使用して、一時的な結果を作成します。
  • PIVOT 演算子を使用します。

構文

次の構文は、SQL Server での PIVOT の使用を示しています。

 SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>

このスクリプトを分解すると、2 つの別々のセクションがあることがわかります。最初のセクションではメイン テーブルからデータを選択し、2 番目のセクションでは PIVOT テーブルの構築方法を決定します。 2 番目の部分には、SUM、FOR、IN などの特別なキーワードも含まれています。 PIVOT 演算子におけるこれらのキーワードの意味を見てみましょう。

和

この演算子は次の目的で使用されます。 値を集計する 指定された列からピボットテーブルで使用されます。値セクションの集計列表示を取得するには、PIVOT 演算子とともに使用する必要があります。

FORキーワード

このキーワードは、PIVOT テーブル ステートメントで使用されます。 PIVOT オペレーターに指示する PIVOT 関数をどの列に適用するか。基本的に、行から列に変換される列名を示します。

INキーワード

このキーワード すべての一意の値をリストします PIVOT テーブルの列として表示される PIVOT 列から。

改行Python

例

さまざまな例を使ってそれを理解しましょう。

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

このステートメントを実行すると、以下の出力が生成されます。ここで、 年の値に対応する北地域と南地域の売上高の計算された合計 。


SQLサーバーピボット

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サーバーピボット

ただし、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;

このステートメントは正常に実行され、地域の値に対応する各年の計算された売上合計が表示されます。

SQLサーバーピボット

3. PIVOT テーブルを取得する前述の例は、考えられるすべての PIVOT 列の値を認識している場合に役立ちます。しかし、来年には列の数が増加すると仮定します。前の例を考慮すると、PIVOT 列として 2010 年、2011 年、および 2012 年があります。ただし、これらの列が将来変更されないという保証はありません。 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&apos; SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR [&apos;+@Pivot_Column+&apos;] IN (&apos;+@Pivot_List+&apos;)) AS PivotTable&apos;; EXEC(@Query) END

このスクリプトでは、2 つのパラメーター化された変数を作成しました。その説明を以下に示します。

@PivotColumn : この変数は、PIVOT テーブルが作成された元のテーブルから列の名前を取得します。 例えば , ここで、「地域」列には、列で利用可能なすべての地域が表示されます。

@PivotList : この変数は、ピボット テーブルの出力列として表示する列リストを受け取ります。

動的ストアド プロシージャの実行

二分探索

動的ストアド プロシージャが正常に作成されたら、それを実行する準備が整います。次のステートメントは、実行時に PIVOT テーブルを表示する動的ストアド プロシージャを呼び出すために使用されます。

 EXEC DynamicPivotTable N&apos;Region&apos;, N&apos;[North], [South]&apos;

ここでは列名 ' を指定しました。 地域 ' を最初のパラメータとして指定し、PIVOT 列リストを 2 番目のパラメータとして指定します。スクリプトを実行すると、次の出力が表示されます。

SQLサーバーピボット

これで、実行時にさらに列を追加して PIVOT テーブルを表示できるようになりますが、最初の 2 つの例では不可能でした。

アンピボットオペレーター

これは、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 する方法を理解しましょう。まず、 元のテーブルとピボットテーブル 次に、このテーブルに 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

コード スニペットを実行すると、次の出力が返されます。

SQLサーバーピボット

以下のコード スニペットは、単一のクエリ内で同じテーブルに対して最初に 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

コード スニペットを実行すると、同じ出力が表示されます。

Javaの抽象クラス
SQLサーバーピボット

注: UNPIVOT プロセスは PIVOT 手順の逆の操作ですが、正確な逆ではありません。 PIVOT が集計を計算し、結果として多数の行を 1 つの行に結合するときに行がマージされているため、UNPIVOT 操作ではテーブルを元のようなものにすることはできません。ただし、PIVOT 演算子が多くの行を 1 つの行にマージしない場合、UNPIVOT 演算子は PIVOT 出力から元のテーブルを取得できます。

結論

この記事では、SQL Server の PIVOT 演算子と UNPIVOT 演算子の完全な概要を説明し、テーブル式を別の式に変換します。 UNPIVOT は PIVOT の逆演算ですが、PIVOT の結果の正確な逆ではないことを決して忘れてはなりません。