行を列に変換する
このレッスンでは、PIVOT を使うと、ある列のユニークな値を複数の列に変換できることを学びました。
paper_shop_monthly_sales のデータを分析すると、作成したいレポートにはこのテーブル構造が適していないことに気づきます。
次のような見た目のレポートを作成したいとします。
|year_of_sale|notebooks|pencils|crayons|
|------------|---------|-------|-------|
| 2018 | 150 | 150 | 80 |
| 2019 | 230 | 130 | 170 |
つまり、行にあるデータを列に変換し、年ごとに単位数を合計したいのです。
前の演習で学んだように、製品名と単位は分割する必要があります。これはサブセレクトで行います。確認してみてください。
この演習はコースの一部です
SQL Server データベースでのデータクレンジング
演習の手順
- 各製品のピボット後の列を選択します。
PIVOT演算子の中で単位の合計を含めます。FOR句の後に、列ヘッダーになる値を含む列名を指定します。PIVOT演算子にpaper_shop_pivotという名前を付けます。
実践的なインタラクティブ演習
このサンプルコードを完成させて、この演習に挑戦してみましょう。
SELECT
year_of_sale,
-- Select the pivoted columns
___,
___,
___
FROM
(SELECT
SUBSTRING(product_name_units, 1, charindex('-', product_name_units)-1) product_name,
CAST(SUBSTRING(product_name_units, charindex('-', product_name_units)+1, len(product_name_units)) AS INT) units,
year_of_sale
FROM paper_shop_monthly_sales) sales
-- Sum the units for column that contains the values that will be column headers
PIVOT (SUM(___) FOR ___ IN (notebooks, pencils, crayons))
-- Give the alias name
AS ___