MySQL/MariaDB でピボット テーブルを実装する方法のサンプル コード

select coalesce(product, '【全商品】') "商品",
    coalesce(channel, '【全チャンネル】') "チャンネル",
    any_value(coalesce(extract(year_month from saledate), '【全月】')) "月",
    合計(金額)「売上高」
販売データから
製品、チャネル別にグループ化し、(saledate から year_month を抽出して) ロールアップします。

select coalesce(product, '【全商品】') "商品", coalesce(channel, '【全チャンネル】') "チャンネル", 
    sum(case extract(year_month from saledate) when 201901 then amount else 0 end) "1月",
    sum(case extract(year_month from saledate) when 201902 then amount else 0 end) "2月",
    sum(case extract(year_month from saledate) when 201903 then amount else 0 end) "三月",
    sum(case extract(year_month from saledate) when 201904 then amount else 0 end) "4月",
    sum(case extract(year_month from saledate) when 201905 then amount else 0 end) "5月",
    sum(case extract(year_month from saledate) when 201906 then amount else 0 end) "六月",
    合計(金額)「合計」
販売データから
製品、ロールアップによるチャネル別にグループ化します。

dを（
 製品、チャネルを選択、
     sum(case extract(year_month from saledate) when 201901 then amount else 0 end) s01,
     sum(case extract(year_month from saledate) when 201902 then amount else 0 end) s02,
     sum(case extract(year_month from saledate) when 201903 then amount else 0 end) s03,
     sum(case extract(year_month from saledate) when 201904 then amount else 0 end) s04,
     sum(case extract(year_month from saledate) when 201905 then amount else 0 end) s05,
     sum(case extract(year_month from saledate) when 201906 then amount else 0 end) s06
 販売データから
 製品、チャネル別にグループ化
）
製品、チャネル、201901 販売日、s01 金額を d から選択
すべて結合
製品、チャネル、201902 販売日、s02 を d から選択
すべて結合
製品、チャネル、201903 販売日、s03 を d から選択
すべて結合
製品、チャネル、201904 販売日、s04 を d から選択
すべて結合
製品、チャネル、201905 販売日、s05 を d から選択
すべて結合
d から製品、チャネル、201906 販売日、s06 を選択します。

グループ連結を選択(
 個別の連結(
  ' sum(case extract(year_month from saledate) when ', dt,
  ' then amount else 0 end) as "', dt, '"')
 ) を @sql に
から （
 select extract(year_month from saledate) as dt
 販売データから
 発売日順に並べる
）d;

@sql を設定する
 = concat('select coalesce(product, ''【全商品】'') "商品", coalesce(channel, ''【全チャネル】'') "チャネル",', @sql,
      '、合計(金額)「合計」
      販売データから
      製品、ロールアップによるチャネル別にグループ化します。');
@sql を選択します。
@sql から stmt を準備します。
ステートメントを実行します。
準備ステートメントの割り当てを解除します。

select coalesce(product, '【全商品】') "商品", coalesce(channel, '【全チャンネル】') "チャンネル", 
    sum(case extract(year_month from saledate) when 201901 then amount else 0 end) as "201901", 
    sum(case extract(year_month from saledate) when 201902 then amount else 0 end) as "201902", 
    sum(case extract(year_month from saledate) when 201903 then amount else 0 end) as "201903", 
    sum(case extract(year_month from saledate) when 201904 then amount else 0 end) as "201904", 
    sum(case extract(year_month from saledate) when 201905 then amount else 0 end) as "201905", 
    sum(case extract(year_month from saledate) when 201906 then amount else 0 end) as "201906", 
    合計(金額)「合計」
販売データから
製品、ロールアップによるチャネル別にグループ化します。

SONAME 'ha_connect' をインストールします。

[mysqld]
プラグインのロード追加 = ha_connect

pivot_salesテーブルを作成する(
 製品varchar(20)がnullではない、
 チャネルvarchar(20)がnullではない、
 `201901` 小数点(10,2) nullでないフラグ=1、
 `201902` 小数点(10,2) nullでないフラグ=1、
 `201903` 小数点(10,2) nullでないフラグ=1、
 `201904` 小数点(10,2) nullでないフラグ=1、
 `201905` 小数点(10,2) nullでないフラグ=1、
 `201906` 小数点(10,2) nullでないフラグ=1
）
エンジン=接続 テーブルタイプ=ピボット
option_list='PivotCol=saledate、FncCol=amount、host=127.0.0.1、user=root、password=p123456、port=3306'
SrcDef='select product,channel,date_format(saledate, ''%Y%m'') saledate,sum(amount) amount from sales_data group by product,channel,date_format(saledate, ''%Y%m'')';

pivot_sales から * を選択します。

製品 | チャンネル | 201901 | 201902 | 201903 | 201904 | 201905 | 201906 |
--------|----------|---------|--------|--------|--------|
オレンジ|JD|41289.00|43913.00|49803.00|49256.00|64889.00|62649.00|
オレンジ|ストア|41306.00|37906.00|48866.00|48673.00|58998.00|58931.00|
オレンジ|タオバオ|43488.00|37598.00|48621.00|49919.00|58530.00|64626.00|
アップル|JD.com|38269.00|40593.00|56552.00|56662.00|64493.00|62045.00|
Apple|ストア|43845.00|40539.00|44909.00|55646.00|56771.00|64933.00|
アップル|タオバオ|42969.00|43289.00|48769.00|58052.00|58872.00|59844.00|
バナナ|JD|36879.00|36981.00|51748.00|54801.00|64936.00|60688.00|
バナナ|ストア|41210.00|39420.00|50884.00|52085.00|60249.00|67597.00|
バナナ|タオバオ|42468.00|41955.00|52780.00|54971.00|56504.00|59213.00|

-- エラーなし select * from pivot_sales
ここで、チャンネルは 'JD.com' です。

-- 構文エラー pivot_sales からチャネルを選択
ここで、チャンネルは 'JD.com' です。