T-SQLで集計関数なしでピボットを実行するサンプルコード

2024-05-15

T-SQLで集計関数なしでピボットを実行する方法

列を値に置き換える

UNPIVOT クエリを使用して、列の値を行の値に変換できます。これにより、列を動的にピボットすることができます。

SELECT
  *
FROM YourTable
UNPIVOT (
  value FOR ColName IN (Col1, Col2, Col3)
) AS unpivoted;

このクエリは、YourTable テーブルからすべての列を選択し、ColName 列の値を Col1、Col2、Col3 列から value 列に展開します。

複数の列をキーとして使用する

PIVOT クエリを使用して、複数の列をキーとして使用してデータをピボットできます。これにより、より複雑なピボットテーブルを作成することができます。

SELECT
  [OrderDate],
  [Product],
  SUM([Quantity]) AS [QuantitySold]
FROM YourTable
PIVOT (
  SUM([Quantity])
  FOR [Product] IN ([Product1], [Product2], [Product3])
) AS pivoted;

このクエリは、YourTable テーブルから OrderDate 列と Product 列を選択し、Quantity 列の値を Product1、Product2、Product3 製品ごとに合計して QuantitySold 列にピボットします。

動的ピボット

CASE 式と動的 SQL を使用して、動的にピボットを実行できます。これにより、さまざまな要件に合わせてピボットを柔軟にカスタマイズすることができます。

DECLARE @pivotSQL NVARCHAR(MAX);

SET @pivotSQL = N'
SELECT
  [OrderDate],
  ' + STUFF((SELECT DISTINCT ',' + QUOTENAME([Product]) FROM YourTable FOR XML PATH(''), TYPE).value('//text()'), 1, 1, '') + ' AS PivotCols,
  ' + STUFF((SELECT SUM([Quantity]) AS Value FROM YourTable GROUP BY [OrderDate], [Product] FOR XML PATH(''), TYPE).value('//text()'), 1, 1, '') + '
FROM YourTable
PIVOT (
  SUM([Quantity])
  FOR [Product] IN (' + STUFF((SELECT DISTINCT QUOTENAME([Product]) FROM YourTable FOR XML PATH(''), TYPE).value('//text()'), 1, 1, '') + ')
) AS pivoted;
';

EXEC sp_executesql @pivotSQL;

このクエリは、YourTable テーブルから OrderDate 列を選択し、Product 列のすべての値をピボット列として動的に生成します。

補足:

上記はほんの一例であり、T-SQL で集計関数なしでピボットを実行する方法は他にもたくさんあります。
特定の要件に最適な方法は、データ構造と要件によって異なります。
より複雑なピボットを実行する場合は、一時テーブルや CTE (共通表式) を使用する必要がある場合があります。

T-SQL で集計関数なしでピボットを実行するサンプルコード

列を値に置き換える

-- サンプルデータ
CREATE TABLE YourTable (
  OrderID INT,
  CustomerID INT,
  OrderDate DATE,
  ProductID INT,
  ProductName NVARCHAR(50),
  Quantity INT
);

INSERT INTO YourTable VALUES
(1, 101, '2023-01-01', 1, 'Product A', 10),
(1, 101, '2023-01-01', 2, 'Product B', 20),
(1, 101, '2023-01-01', 3, 'Product C', 30),
(2, 102, '2023-02-01', 1, 'Product A', 15),
(2, 102, '2023-02-01', 2, 'Product B', 25),
(2, 102, '2023-02-01', 3, 'Product C', 35);

-- 列を値に置き換える
SELECT
  *
FROM YourTable
UNPIVOT (
  value FOR ColName IN (ProductID, ProductName, Quantity)
) AS unpivoted;

OrderID CustomerID OrderDate ColName value
------- -------- -------- -------- --------
1       101      2023-01-01 ProductID 1
1       101      2023-01-01 ProductName Product A
1       101      2023-01-01 Quantity 10
1       101      2023-01-01 ProductID 2
1       101      2023-01-01 ProductName Product B
1       101      2023-01-01 Quantity 20
1       101      2023-01-01 ProductID 3
1       101      2023-01-01 ProductName Product C
1       101      2023-01-01 Quantity 30
2       102      2023-02-01 ProductID 1
2       102      2023-02-01 ProductName Product A
2       102      2023-02-01 Quantity 15
2       102      2023-02-01 ProductID 2
2       102      2023-02-01 ProductName Product B
2       102      2023-02-01 Quantity 25
2       102      2023-02-01 ProductID 3
2       102      2023-02-01 ProductName Product C
2       102      2023-02-01 Quantity 35

複数の列をキーとして使用する

-- サンプルデータ
CREATE TABLE YourTable (
  OrderID INT,
  CustomerID INT,
  OrderDate DATE,
  ProductID INT,
  ProductName NVARCHAR(50),
  Country NVARCHAR(50),
  Quantity INT
);

INSERT INTO YourTable VALUES
(1, 101, '2023-01-01', 1, 'Product A', 'USA', 10),
(1, 101, '2023-01-01', 2, 'Product B', 'USA', 20),
(1, 101, '2023-01-01', 3, 'Product C', 'Canada', 30),
(2, 102, '2023-02-01', 1, 'Product A', 'Canada', 15),
(2, 102, '2023-02-01', 2, 'Product B', 'USA', 25),
(2, 102, '2023-02-01', 3, 'Product C', 'USA', 35);

--

T-SQLで集計関数なしでピボットを実行するその他の方法

CASE 式と SUM() 関数を使用する

この方法は、シンプルなピボットテーブルを作成する場合に役立ちます。

SELECT
  [OrderDate],
  [Country],
  [Product],
  SUM(CASE WHEN [ProductID] = 1 THEN [Quantity] ELSE 0 END) AS [QuantitySold1],
  SUM(CASE WHEN [ProductID] = 2 THEN [Quantity] ELSE 0 END) AS [QuantitySold2],
  SUM(CASE WHEN [ProductID] = 3 THEN [Quantity] ELSE 0 END) AS [QuantitySold3]
FROM YourTable
GROUP BY
  [OrderDate],
  [Country],
  [Product]
ORDER BY
  [OrderDate],
  [Country],
  [Product];

XML を使用する

SELECT
  [OrderDate],
  [Country],
  p.value('//Product/@ProductID') AS [Product],
  p.value('//Quantity/text()') AS [QuantitySold]
FROM YourTable
CROSS APPLY
(
  SELECT
    CAST(
      '<Product ProductID="' + CONVERT(NVARCHAR(50), [ProductID]) + '">'
      + CONVERT(NVARCHAR(MAX), [Quantity])
      + '</Product>'
      AS XML
    )
  FOR XML PATH('')
) AS x
GROUP BY
  [OrderDate],
  [Country],
  p.value('//Product/@ProductID')
ORDER BY
  [OrderDate],
  [Country],
  [Product];

ウィンドウ関数を使用する

SELECT
  [OrderDate],
  [Country],
  [Product],
  SUM([Quantity]) OVER (PARTITION BY [OrderDate], [Country], [Product]) AS [QuantitySold]
FROM YourTable
ORDER BY
  [OrderDate],
  [Country],
  [Product];

これらの方法はほんの一例であり、T-SQL で集計関数なしでピボットを実行する方法は他にもたくさんあります。特定の要件に最適な方法は、データ構造と要件によって異なります。