T-SQL で PIVOT（ピボット）を使って集計する

T-SQL で PIVOT（ピボット）を使って集計してみよう！

今回は関係演算子 PIVOT を使用して、Employee ごと月ごとの売り上げを集計してみましょう。

サンプル用のテーブルとデータを作る

次のスクリプトを実行して、今回使用する Employee テーブルと Sales テーブルを作って、データをインサートしておいてください。 

CREATE TABLE Employee (
	EmployeeID	INT NOT NULL PRIMARY KEY,
	EmployeeName	NVARCHAR(100) NOT NULL
);

CREATE TABLE Sales (
	SalesID		INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	EmployeeID	INT NOT NULL,
	SalesDate	DATE NOT NULL,
	SalesAmount	DECIMAL(18,4) NOT NULL,
	CONSTRAINT FK_Sales_Employee FOREIGN KEY (EmployeeID) 
				REFERENCES Employee (EmployeeID)
);

INSERT INTO Employee 
	(EmployeeID, EmployeeName)
VALUES 
	(1001, 'Jake'),
	(1002, 'Nick'),
	(1003, 'Daniel'),
	(1004, 'Mike');

INSERT INTO Sales 
	(EmployeeID, SalesDate, SalesAmount)
VALUES
	(1001, '2016-01-01', 100),
	(1001, '2016-02-05', 120),
	(1001, '2016-02-10', 200),
	(1001, '2016-03-01', 110),
	(1002, '2016-03-02', 50),
	(1002, '2016-03-02', 80),
	(1002, '2016-03-05', 90),
	(1003, '2016-01-03', 150),
	(1003, '2016-01-05', 160),
	(1004, '2016-02-06', 300),
	(1004, '2016-04-01', 50);

以下のようになるはずです。

Pivot 1


2016 年の 1 月から 3 月までの各 Employee の売り上げの合計を集計する

先ほど作った Employee テーブルと Sales テーブルのデータを元に、関係演算子 PIVOT を使用して、2016 年の 1 月から 3 月までの各 Employee ごとの売り上げの合計を集計するクエリーは以下の通りです。 

SELECT	EmployeeID,
	EmployeeName,
	[201601], [201602], [201603]
FROM 
	(SELECT S.EmployeeID,
		E.EmployeeName,
		LEFT(CONVERT(NVARCHAR, S.SalesDate, 112), 6) AS YYYYMM,
		S.SalesAmount
	 FROM	Sales AS S
			INNER JOIN Employee AS E
				ON S.EmployeeID = E.EmployeeID
	 WHERE	S.SalesDate BETWEEN '2016-01-01' AND '2016-03-31') AS T
PIVOT
(
	SUM(SalesAmount)
	FOR YYYYMM IN
		([201601], [201602], [201603])
) AS PVT
ORDER BY EmployeeID;

Pivot 2


では、クエリを見てみましょう。

Pivot 3

まず対象となるデータを取得する Select 文を青で囲まれた部分に書きます。

青で囲まれた SELECT 句は以下のカラムを含みます。

  • Pivot させずに結果として表示したいカラム: EmployeeID と EmployeeName
  • 集計する際にカラムヘッダーとなる値が入ったカラム: SalesDate から変換された YYYYMM （年月）
  • 集計する値が入っているカラム: SalesAmount

PIVOT のセクションで、オレンジ色で囲まれた部分で、どのカラムをどのように集計するかを指定します。

今回は SalesAmount の合計にしましたが、COUNT にすれば各月の Sales レコードの件数、MAX にすれば一番高かった SalesAmount などが取得できます。

そして、先ほど青で囲まれた部分で「カラムヘッダーとなる値が入ったカラム」として用意したカラムの値のうち、実際に PIVOT(回転) させてカラムヘッダーとして使用したい値を黄緑の部分に指定します。

今回は 201601 から 201603 までとしました。

一番上の Select 句では、Pivot させずに結果として表示したいカラムと黄緑で指定した値をカラムとして指定しています。

2016 年の Employee の各月の売り上げを集計する

先ほどと Pivot するカラムを反対にして、Employee の各月の売り上げを集計することができます。

EmployeeID 1001 と 1003 の月ごとの SalesAmount の合計を集計するクエリーは以下の通りです。 

SELECT	YYYYMM,
	[1001], [1003]
FROM 
	(SELECT	LEFT(CONVERT(NVARCHAR, SalesDate, 112), 6) AS YYYYMM,
		EmployeeID,
		SalesAmount
	 FROM	Sales
	 WHERE	SalesDate BETWEEN '2016-01-01' AND '2016-03-31') AS T
PIVOT
(
	SUM(SalesAmount)
	FOR EmployeeID IN
		([1001], [1003])
) AS PVT
ORDER BY YYYYMM;

Pivot 4

  • Pivot させずに結果として表示したいカラム: SalesDate から変換された YYYYMM （年月）
  • 集計する際にカラムヘッダーとなる値が入ったカラム: EmployeeID
  • 集計する値が入っているカラム: SalesAmount

2016年の1月から3月までの各 Employee・月々の日ごとの売り上げを取得する

先ほどは月々の売り上げの集計でしたが、次は PIVOT を使って、各 Employee・月々の日ごとの売り上げを取得してみましょう。 

SELECT	EmployeeID,
	EmployeeName,
	YYYYMM,
	[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]
FROM 
	(SELECT	S.EmployeeID,
		E.EmployeeName,
		LEFT(CONVERT(NVARCHAR, S.SalesDate, 112), 6) AS YYYYMM,
		DAY(S.SalesDate) AS DD,
		S.SalesAmount
	FROM	Sales AS S
			INNER JOIN Employee AS E
				ON S.EmployeeID = E.EmployeeID
	WHERE	S.SalesDate BETWEEN '2016-01-01' AND '2016-03-31') AS T
PIVOT
(
	SUM(SalesAmount)
	FOR DD 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])
) AS PVT
ORDER BY	EmployeeID,
		YYYYMM;

Pivot 5

  • Pivot させずに結果として表示したいカラム: EmployeeID、EmployeeName、SalesDate から変換された YYYYMM （年月）
  • 集計する際にカラムヘッダーとなる値が入ったカラム: SalesDate から変換された DD
  • 集計する値が入っているカラム: SalesAmount
© 2010-2024 SQL Server 入門