SQL見るだけクエリ#86 SQL PIVOT演算子を使って、行を列に変換する方法

記入日：2023-04-04　編集日：2023-04-04

SQL PIVOT演算子を使って、行を列に変換する方法。

PIVOT演算子を使って、行を列に変換

PIVOT演算子は、行から列にデータを変換するために使用されます。具体的には、ある列の値を列名に変換して、その列の値を新しい行として生成します。例えば、以下のようなsalesテーブルがあるとします。

region | year | sales_amt
-------|------|----------
North  | 2019 | 100
North  | 2020 | 150
North  | 2021 | 200
South  | 2019 | 50
South  | 2020 | 75
South  | 2021 | 100

このテーブルをPIVOT演算子を使用して変換するには、以下のようなクエリを実行します。

SELECT region, [2019], [2020], [2021]
FROM (
    SELECT region, year, sales_amt
    FROM sales
) AS t
PIVOT (
    SUM(sales_amt)
    FOR year
    IN ([2019], [2020], [2021])
) AS p;

このクエリの結果は以下のようになります。

region | 2019 | 2020 | 2021
-------|------|------|------
North  | 100  | 150  | 200
South  | 50   | 75   | 100

この結果では、元のテーブルのregion列をそのまま保持しながら、year列の値が列名として使用され、sales_amt列の値が新しい行として生成されています。つまり、変換前のテーブルの行数と変換後のテーブルの行数は同じですが、変換後のテーブルは列数が増えています。

このように、PIVOT演算子を使用することで、データの可視化や集計処理などに便利に利用することができます。ただし、PIVOT演算子は、複雑な構文を持っているため、使用する場合は注意が必要です。

使用に注意が必要な場面とは？

PIVOT演算子の構文が複雑であるため、注意が必要な場面は以下のようなものがあります。

1. 変換前のデータが大量にある場合
   PIVOT演算子は、変換前のデータの全ての列名を列挙する必要があるため、データ数が多い場合には手間がかかります。また、列名にスペースや特殊文字が含まれる場合には、列名をクォーテーションで囲む必要があるため、手間が増えます。
2. 変換後の列名が可変的である場合
   PIVOT演算子は、変換後の列名が固定的であるため、変換後の列名が可変的である場合には使用できません。例えば、あるテーブルには異なる日付のデータが含まれており、それを列名に変換したい場合には、PIVOT演算子では変換できません。
3. 変換前のデータが複数の列から構成される場合
   PIVOT演算子は、単一の列から変換を行うため、複数の列から構成されるデータを変換する場合には、PIVOT演算子を複数回実行する必要があります。また、変換前のデータが複数の列から構成される場合には、変換後の列名を生成するための式を書く必要があるため、構文が複雑になります。

以上のような場面では、PIVOT演算子を使用することが難しい場合があります。そのため、PIVOT演算子を使用する前に、データの構造や変換後の形式を十分に理解し、必要に応じて代替手段を検討することが重要です。