日日是Oracle APEX: 表をピボットさせて対話グリッドで操作する

2020年4月22日水曜日

表をピボットさせて対話グリッドで操作する

表題の相談を受けました。まだ十分ではないですが、何かの役に立つかもしれませんので、記事にしておきます。

画面に２つの対話グリッドがありますが、両方とも同じ表DEMO_PRODUCT_PRICESを参照しています。上の対話グリッドは、日付のデータが列になるように、対話グリッドに与えるSQLでピボット操作を行っています。下のグリッドは表DEMO_PRODUCT_PRICESをそのままソースとしています。最初に製品名OraPodsの日付ごとの時価を新規入力しています。その後、OraPodsのエントリを更新し、最後にOraPodsの行を削除しています。下にある対話グリッドを見ると、複数の行を対象にしたデータ操作が行われていることがわかります。

サンプルに使用している表定義

今回のサンプルを作成するにあたって、以下の表を作りました。使用している表はひとつだけです。

表DEMO_PRODUCT_PRICESは、ある特定の製品PRODUCT_NAMEがSAMPLING_DATEである月に市場価格がいくらであったかを保持しています。

ピボット後の列の定義

列SAMPLING_DATEのデータをピボット処理に使用します。ピボットして列とするデータをDEMO_PRODUCT_PRICES_PKG.G_PIVOT_COLUMNSとして定義しています。

対話グリッドのソースSQL

製品名(PRODUCT_NAME)を縦軸、年月(SAMPLING_DATE)を横軸として、市場価格(MARKET_PRICE)を返すSQLは、おおよそ以下のようになります。

select * from 
  (
    select product_name as pid, product_name as product_name, sampling_date, market_price from demo_product_prices) 
    pivot (sum(market_price) for sampling_date in (
      '202301' as "202301",'202302' as "202302",'202303' as "202303",'202304' as "202304",'202305' as "202305",'202306' as "202306",'202307' as "202307",'202308' as "202308",'202309' as "202309",'202310' as "202310",'202311' as "202311",'202312' as "202312"
    )
  )

このピボット処理を行なうSQLを生成するPL/SQLコードを、対話グリッドのソースとして与えます。

ソースのタイプとしてSQL問合せを戻すPL/SQLファンクション本体を指定します。

画面では、以下の設定になります。

対話グリッドの設定

select product_nam as pid, product_name as product_nameとして、同じPRODUCT_NAMEを２列にしているのは、PIDの方を対話グリッドの主キーとして扱うためです。PRODUCT_NAMEの方は通常のデータ入力に使用します。PIVOT句の利用では必ず集計関数を使わないといけないので、sum(market_price)として時価の合計にしています。PRODUCT_NAMEとSAMPLING_DATEの組み合わせで一意となる前提ですので、合計をとっても（値が１つしかないので)値は変わりません。PIVOT処理の列となる年月のデータはSAMPLING_DATEに含まれる値で変わります。

ソースとなるPL/SQLコードを定義して保存すると、対話グリッドに列が認識されます。列の中にPIDが含まれるので、タイプを非表示、問合せのみをON、主キーをONにします。

この他にはPRODUCT_NAMEは必須の値にしておくべきでしょう。