LISTAGG

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


               .-ALL------.                                                   
>>-LISTAGG--(--+----------+--string-expression--+-------------------------+-->
               '-DISTINCT-'                     '-,--separator-expression-'   

   .-ON OVERFLOW ERROR------------------------------------------------.      
>--+------------------------------------------------------------------+--)-->
   |                       .-'...'---------------.  .-WITH COUNT----. |      
   '-ON OVERFLOW TRUNCATE--+---------------------+--+---------------+-'      
                           '-overflow-characters-'  '-WITHOUT COUNT-'        

>--+-----------------------------------------------------------------+-><
   |                            .-,-----------------------------.    |   
   |                            V                      .-ASC--. |    |   
   '-WITHIN GROUP--(--ORDER BY----sort-key-expression--+------+-+--)-'   
                                                       '-DESC-'

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

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

string-expression

集約するストリング値を指定する式。この式は、組み込みデータ・タイプであるストリング、数値、日時のいずれかの値を戻す必要があります。数値または日時のデータ・タイプの値は、関数の評価の前に VARCHAR に暗黙的にキャストされます。

separator-expression

NULL でない string-expression 値の間で使用するストリングを定義する式。この式は、組み込みデータ・タイプであるストリング、数値、日時のいずれかの値を戻す必要があります。数値または日時のデータ・タイプの値は、関数の評価の前に VARCHAR に暗黙的にキャストされます。 separator-expression にスカラー全選択、列参照、および非 deterministic 関数および外部アクションを含む関数への参照を含めてはなりません。

separator-expression が指定されない場合や、separator-expression が NULL 値の場合、 string-expression 値の間の区切りはありません。

ON OVERFLOW ERROR または ON OVERFLOW TRUNCATE

集約された結果ストリングの実際の長さが結果の長さを超える場合の動作を指定します。デフォルトは ON OVERFLOW ERROR です。

ON OVERFLOW ERROR

結果ストリングの実際の長さが結果の長さを超える場合、エラーが戻されるように指示します。

ON OVERFLOW TRUNCATE

結果ストリングの実際の長さが結果の長さを超える場合、集約された結果ストリングが切り捨てられるように指示します。切り捨ては、ストリング値の末尾で行われます。結果ストリングは、定義されていれば最後の separator-expression を含む、完全な項目を含みます。

string-expression がバイナリー・ストリングまたは非 Unicode グラフィック・ストリングである場合、このオプションは使用できません。

'...' または overflow-characters

切り捨てが行われたことを示すために、結果ストリングの末尾に付加される文字ストリング定数を指示します。

'...': 最後の完全な項目のすぐ後に、3 つのピリオド文字が付加されるように指示します。これはデフォルトです。
overflow-characters: 最後の完全な項目のすぐ後に付加される文字ストリング定数を指示します。

WITH COUNT または WITHOUT COUNT

切り捨てられた値の数を結果ストリングの最後に含めるかどうかを指示します。デフォルトは WITH COUNT です。

WITH COUNT: ストリングから切り捨てられた値の数が、結果ストリングの最後に付加されます。括弧内の数値として形式設定されます。例えば、10 項目が切り捨てられた場合、(10) がストリングの最後に組み込まれます。
WITHOUT COUNT: 切り捨てられた項目の数を表すものは何も戻されません。

WITHIN GROUP

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

WITHIN GROUP が指定されていない場合、結果に含まれるストリングの順序付けは一律には決まりません。

ORDER BY

集約で処理される、同じグループ化セットからの行の順序を指定します。ORDER BY 文節を指定しない場合、または ORDER BY 文節ではソート・キー値の順序を区別できない場合、同じグループ内の行は任意に順序付けられます。

sort-key-expression

列名または式のどちらかのソート・キー値を指定します。列または式のデータ・タイプは、DATALINK 値または XML 値であってはなりません。

集約したエレメントの順序付けは、ソート・キーの値に基づいて行われます。

sort-key-expression の長さ属性の合計は 3.5 ギガバイトを超えてはなりません。

ASC: sort-key-expression を昇順で処理します。これはデフォルトです。
DESC: sort-key-expression を降順で処理します。

LISTAGG 関数を含むステートメントの実行時に *HEX 以外の照合順序が有効で、しかも sort-key-expressions が SBCS データ、混合データ、または Unicode データの場合、結果は重み付けされた値の比較によって求められます。この重み付けされた値は、ソート・キー式 に照合順序を適用して得られます。

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

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

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

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

集約された結果ストリングの実際の長さが結果の長さを超える場合は、 ON OVERFLOW 節によって動作が決まります。

結果の CCSID は string-expression の CCSID です。

LISTAGG 関数を含むステートメントの実行時に *HEX 以外の照合順序が有効で、しかも sort-key-expression が SBCS データ、混合データ、または Unicode データの場合、結果は重み付けされた値の比較によって求められます。この重み付けされた値は、sort-key-expression に該当の照合順序を適用することにより得られます。

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

規則

LISTAGG を OLAP 指定の一部として使用することはできません。

例

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

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

以下の結果を生成します。

WORKDEPT   EMPLOYEES
A00        HAAS, HEMMINGER, LUCCHESSI, O'CONNELL, ORLANDO
B01        THOMPSON
C01        KWAN, NATZ, NICHOLLS, QUINTANA
D11        ADAMSON, BROWN, JOHN, JONES, LUTZ, PIANKA, SCOUTTEN, STERN, WALKER
                YAMAMOTO, YOSHIMURA
D21        JEFFERSON, JOHNSON, MARINO, MONTEVERDE, PEREZ, PULASKI, SMITH
E01        GEYER
E11        HENDERSON, PARKER, SCHNEIDER, SCHWARTZ, SETRIGHT, SMITH, SPRINGER
E21        ALONZO, GOUNOT, LEE, MEHTA, SPENSER, WONG