LISTAGG 集約関数

LISTAGG 関数は、ストリングを連結することにより、一連のストリング・エレメントを 1 つのストリングに集約します。オプションで、隣接する入力ストリング同士の間に挿入するセパレーター・ストリングを指定することができます。

スキーマは SYSIBM です。

LISTAGG 関数は、特定のグループのストリング値の集合を 1 つのストリングに集約します。これは、WITHIN GROUP 節で指定された順序に基づいて string-expression の値を追加することによって行います。

この関数は、最初の引数から NULL 値を取り除いて得られた値の集合に対して適用されます。 DISTINCT を指定すると、重複する string-expression 値は除去されます。 NULL 値ではない separator 引数を指定すると、NULL 以外の string-expression のそれぞれの値ペアの間にその値が挿入されます。

ストリング式集約するストリング値を指定する式。この式は、組み込みの文字ストリング、グラフィック・ストリング、バイナリー・ストリング、数値、ブール値、または日時値を返す必要があります。

値が文字ストリング、GRAPHIC ストリング、バイナリー・ストリングのいずれでもない場合、あるいは値が CLOB である場合は、関数の評価の前に VARCHAR に暗黙的にキャストされます。
DBCLOB の値は、関数の評価の前に VARGRAPHIC に暗黙的にキャストされます。
値を BLOB にすることはできません (SQLSTATE 42815)。

分離文字NULL でない string-expression 値の間で使用する区切りストリングを定義する定数式。この式は、組み込みデータ・タイプであるストリング、数値、日時のいずれかの値を戻す必要があります。ストリング・データ・タイプでない値は、関数の評価の前に VARCHAR に暗黙的にキャストされます。 CLOB および DBCLOB は、暗黙的キャストを介してサポートされます。 CLOB の値は、関数の評価の前に VARCHAR に暗黙的にキャストされます。 DBCLOB の値は、関数の評価の前に VARGRAPHIC に暗黙的にキャストされます。 separator のデータ・タイプを BLOB にすることはできません (SQLSTATE 42815)。

区切り文字は、定数、特殊レジスター、または変数にすることができます。さらに、非決定論的な関数も外部アクションを取る関数も含まれていない式であれば、定数、特殊レジスター、または変数に基づく式にすることもできます。

WITHIN GROUP

集約するときに、グループ化集合の中の指定順序で配列されることを示します。

WITHIN GROUP が指定されず、同じ SELECT 節の中に順序付けが指定されたその他の LISTAGG、ARRAY_AGG、または XMLAGG が組み込まれていない場合、結果に含まれるストリングの順序付け方法は一律には決まりません。 WITHIN GROUP が指定されず、同じ SELECT 節に順序付けを指定する XMLAGG、ARRAY_AGG、または LISTAGG が複数存在する場合、LISTAGG 関数呼び出しの結果ではそれと同じ順序付けが使用されます。

ORDER BY: 集合内の処理対象の、同じグループ化集合に属する行の順序を指定します。 ORDER BY 節が列データの順序を識別できない場合、同一のグループ化集合内の行は任意に順序付けられます。; ソート・キーは、列名または sort-key-expression のどちらでも構いません。ソート・キーが定数の場合、これは出力列の位置を (照会の ORDER BY 節におけるように) 参照せず、単なる定数であり、ソート・キーはないことを意味します。
ASC: sort-key を昇順で処理します。これはデフォルト・オプションです。
DESC: sort-key を降順で処理します。

結果

LISTAGG の結果データ・タイプは、string-expression のデータ・タイプに基づいて決まります。

表 1. 結果のデータ・タイプとデータ長の決定
string-expression のデータ・タイプ	結果のデータ・タイプとデータ長
CHAR(n) または VARCHAR(n)	VARCHAR (MAX (4000, n))
GRAPHIC(n) または VARGRAPHIC(n)	VARGRAPHIC (MAX (2000, n))
BINARY(n) または VARBINARY(n)	VARBINARY (MAX (4000, n))

結果のデータ・タイプのストリング単位は、string-expression のデータ・タイプのストリング単位と同じになります。

派生サイズを使用して結果のサイズを決定する場合、結果のデータ・タイプは、VARCHAR (4000)、VARBINARY (4000)、または VARGRAPHIC (2000) を超えて、結果のデータ・タイプの最大値を超えることができます。次の例では、戻りのデータ・タイプ VARCHAR(10000) が正常に生成されます。

   LISTAGG(CAST(NAME AS VARCHAR(10000)), ',')

集約結果のストリングの実際の長さが結果データ・タイプの最大値を超える場合、エラーが戻されます (SQLSTATE 22001)。

結果は NULL 値の場合もあります。この関数が空集合に適用されるか、集合に含まれるすべての string-expression の値が NULL 値である場合、結果は NULL 値になります。

ルール

LISTAGG で DISTINCT を指定する場合は、ORDER BY に指定する sort-key と string-expression を同じにする必要があります (SQLSTATE 42822)。 string-expression が暗黙的にキャストされる場合は、対応する同じキャスト指定を明示的に sort-key に含める必要があります。
SELECT 節に ARRAY_AGG 関数が含まれている場合は、同じSELECT 節内の ARRAY_AGG、LISTAGG、XMLAGG、および XMLGROUP 関数のすべての呼び出しが以下のいずれかの基準を満たしている必要があります (SQLSTATE 428GZ)。
- 同じ順序を指定する
- 順序を指定しない
- DISTINCT を指定した LISTAGG の string-expression 引数を、ARRAY_AGG の ORDER BY 節の sort-key 式と一致させる
LISTAGG は OLAP 指定の一部としては使用できません (SQLSTATE 42887)。

例

部門別にグループ化したコンマ区切りの名前リストをアルファベット順で生成します。

   SELECT workdept, 
         LISTAGG(lastname, ', ') WITHIN GROUP(ORDER BY lastname)
         AS employees 
      FROM emp 
      GROUP BY workdept