（※このトピックスは、Valence開発元（米CNX社）のブログ記事を翻訳・再編集したものとなります。原文記事は、コチラとなります。）
エンドユーザーからのアプリのリクエストに対応していると、ヘッダーレベルのデータを表示するグリッドの中に詳細レベルのファイルのデータを表示したいというケースに遭遇することがあります。 例えば、カスタマーサービス担当者が、現在はヘッダー情報しか表示していない顧客注文アプリに明細行アイテムのリストを統合するように依頼することがあります。
このような要望を聞くと、「では、注文ヘッダーの行をクリックして、ドリルダウンしてすべての項目を見ることができるようにしたいのですね」と答えるかもしれません。
“いやいや、何もクリックしないで、注文に含まれるすべての製品番号が、顧客名と出荷先住所と一緒に1つのセルに表示されればいいんだ！”とユーザーは言うのです。
そして、正規化されたデータの概念と、彼らが求めていることの複雑さを説明する試みが始まるのです。ただ、IBM i OS/400 V7R2以降であれば、簡単なSQLトリックを使用して、ユーザーが望むと思うものを素早く提供し、それが実際に機能するかどうかを確認することができます。
このトリックの鍵は、データソースにあるLISTAGG（「集計関数」）と呼ばれる、やや不明瞭なSQL関数です。簡単に言うと、LISTAGGは通常複数行で縦に表示されるデータ要素を、通常はカンマで区切られた1つの横のリストに圧縮して表示するものです。IBMの公式ドキュメントはこちらでご覧いただけますが、簡単な例で説明した方が理解しやすいでしょう。それでは、顧客注文の詳細を顧客注文のヘッダーリストに混ぜるというユーザーリクエストに挑戦してみましょう…
ここでは、顧客注文のヘッダーファイルであるDEMOORD_Hから要素を取り込む、シンプルなSQLデータソースから始めます（データソースの作成にまだ慣れていない方は、この1分間のビデオで、その方法を簡単にご覧ください）。
select ORDERNO, STATUS, SHPCITY, SHPSTATE, SCHDATE
from DEMOORD_H
order by ORDERNO
この文の結果、次のようなデータソースプレビューパネルが表示されるはずです。
では、ユーザが望むように、詳細ファイルDEMOORD_Dから各注文の項目を取り込んでみましょう。しかし、メインのFROM句でファイルに結合するのではなく、魔法のLISTAGG関数を使用する注文番号でリンクされたサブクエリを次のように挿入します（太字が変更点）。
select ORDERNO, STATUS, SHPCITY, SHPSTATE, SCHDATE,
(select listagg(trim(ITEM),', ') from DEMOORD_D as D
where D.ORDERNO=H.ORDERNO) as ITEMS
from DEMOORD_H as H
order by ORDERNO
ご推察の通り、LISTAGGの2つのパラメータは、注文の項目番号をトリミングして、区切り文字としてカンマ+スペースを使用するように要求しています。この文の結果、ヘッダーデータと詳細データが混在し、次のようになります（カンマ区切りのITEMSカラムに注目）。
もちろん、この形式でグリッドアプリを配信した場合、ユーザーはアルファベット順に表示するよう求めてくることはほぼ確実です。 そのためには、次のように「within group(order by xxx)」という少し特殊な節を文に追加する必要があります。
select ORDERNO, STATUS, SHPCITY, SHPSTATE, SCHDATE,
(select listagg(trim(ITEM),', ')
within group(order by ITEM)
from DEMOORD_D as D
where D.ORDERNO = H.ORDERNO) as ITEMS
from DEMOORD_H as H
order by ORDERNO
その結果、次のようになります。
最後に、注文に同じ項目が複数回含まれる場合（この例の注文10001のように）に対応するために、LISTAGG関数の中にDISTINCTを追加して、そのような項目をリストに一度だけ含めるようにすることができます。
select ORDERNO, STATUS, SHPCITY, SHPSTATE, SCHDATE,
(select listagg(distinct trim(ITEM),', ')
within group(order by ITEM)
from DEMOORD_D as D
where D. ORDERNO = H.ORDERNO) as ITEMS
from DEMOORD_H as H
order by ORDERNO
このような結果になりました。
もちろん、ヘッダー行に関連する詳細レコードの数が非常に多い場合（たとえば、数百行に及ぶ巨大な顧客注文）、LISTAGGを使用するアプローチには特別な処理が必要になる場合があります。このような場合の対処法や、LISTAGGを活用する他の強力な方法の例については、RPGPGM.comのSimon HutchinsonとIT JungleのTed Holtによる投稿をご覧ください。
LISTAGG 機能は V7R4 または V7R3(TR2+) または V7R2(TR6+) を実行している IBM i で利用可能です。また、LISTAGG 出力のソートに “within group(order by xxx)” 節を使用するには、最新の Valence ビルド (6.0.20210226.1) を実行している必要があります。