Hitachi

Hitachi Advanced Database SQL Reference

7.23.14 LISTAGG

Concatenate a series of ordered values and insert a separator character string between the values to obtain a string.

Note

In the description of 7.23.14 LISTAGG, the LISTAGG set function is denoted as LISTAGG.

Organization of this subsection

(1) Specification format

LISTAGG-set-function::= LISTAGG ([{ALL|DISTINCT}] value-expression
                              [,LISTAGG-separator-character-string]
                              [,maximum-length-of-the-LISTAGG-result-character-string]
                              [ON OVERFLOW overflow-behavior]
                             ) WITHIN-group-specification
 
  overflow-behavior::= {ERROR|TRUNCATE [truncated-string-suffix] WITHOUT COUNT}
 
  WITHIN-group-specification::= WITHIN GROUP(ORDER BY sort-specification-list)

To Page Top

(2) Explanation of specification format

●[{ALL|DISTINCT}]: value-expression
{ALL|DISTINCT}:

Specify whether to eliminate duplicate values in the value derived from the value expression.

ALL: Do not eliminate duplicate values even if there are duplicate values.

DISTINCT: If there are duplicate values, consolidate the duplicate values into a single value.

If neither ALL nor DISTINCT is specified, the system assumes that ALL is specified.

value-expression:

Specify the value expression for which the LISTAGG aggregate is determined. For details about value expressions, see 7.21 Value expression.

Specify numeric data (INTEGER, BIGINT, SMALLINT, DECIMAL, NUMERIC, DOUBLE PRECISION, FLOAT, REAL) or character string data (CHAR, VARCHAR, STRING) for the value expression. For details about numeric data and character string data, see 6.2.1 List of data types.

The value derived from the value expressed is called a LISTAGG aggregated string.

Note

If the specified value expression is numeric data, the numeric data value derived from the value expression is converted to a character string data value. The data conversion rules in this case follow the rules for converting numeric data to character string data with the scalar function CONVERT (when no number format is specified). For details about the rules when converting numeric data to character string data with the scalar function CONVERT, see (c) Rules for converting to character string data in (5) Rules in 8.13.5 CONVERT.

● LISTAGG-separator-character-string

Specify the separator character string to be inserted between the aggregated string and aggregated string of LISTAGG in the form of a character string literal. For details about character string literals, see 6.3 Literals.

An example of the LISTAGG-separator-character-string specification and the LISTAGG result is as follows. Bob, Mike, Nancy, Stephanie, and Tom used in the example are the aggregated strings of LISTAGG.

Example:

  • '|' is specified in LISTAGG-separator-character-string

    The LISTAGG result is as follows:

    Bob|Mike|Nancy|Stephanie|Tom

  • ',' is specified in LISTAGG-separator-character-string

    The LISTAGG result is as follows:

    Bob,Mike,Nancy,Stephanie,Tom

  • ':::' is specified in LISTAGG-separator-character-string

    The LISTAGG result is as follows:

    Bob:::Mike:::Nancy:::Stephanie:::Tom

The following specification rules apply:

  • If LISTAGG-separator-character-string is omitted, the LISTAGG separator character string is assumed to have character data of actual length 0 bytes. The following is an example of the LISTAGG result when the LISTAGG-separator-character-string specification is omitted.

    Example:

    BobMikeNancyStephanieTom

  • The length of the LISTAGG separator character string must be less than or equal to the specified value (in bytes) of maximum-length-of-the-LISTAGG-result-character-string.

● maximum-length-of-the-LISTAGG-result-character-string

Specify the maximum length of the LISTAGG result character string (the length of the VARCHAR type definition) in bytes.

Example:
SELECT LISTAGG("C1",'|',20) WITHIN GROUP (ORDER BY "C1") FROM "T1"

The underlined portion is the maximum-length-of-the-LISTAGG-result-character-string specification. In this case, the maximum length of the LISTAGG result character string is 20 bytes (VARCHAR(20)).

The following specification rules apply:

  • Specify an unsigned integer literal in the range from 3 to 32,000 or 32,000,000 as the maximum-length-of-the-LISTAGG-result-character-string.

  • If maximum-length-of-the-LISTAGG-result-character-string is omitted, the maximum length of the LISTAGG result character string is assumed to be 1,024 bytes (VARCHAR(1024)).

● ON OVERFLOW overflow-behavior
overflow-behavior: = {ERROR| TRUNCATE [truncated-string-suffix] WITHOUT COUNT}

Overflow behavior specifies the behavior if the data length of the aggregated data exceeds the maximum length of the LISTAGG result character string.

Note

The aggregated data is character data (including LISTAGG separator character strings) aggregated by LISTAGG.

ERROR:

If the data length of the aggregated data exceeds the maximum length of the LISTAGG result character-string, the SQL statement is an error. An example of an SQL statement that generates an error is given below.

Example:

  • aggregated data:'Bob|Mike|Nancy|Stephanie|Tom' ←28 bytes

  • maximum-length-of-the-LISTAGG-result-character-string specification: 15 bytes

In the above case, the SQL statement is an error because the data length of the aggregated data (28 bytes) exceeds the maximum length of the LISTAGG result character string (15 bytes).

TRUNCATE[truncated-string-suffix] WITHOUT COUNT:

If the data length of the aggregated data exceeds the maximum length of the LISTAGG result character string, the SQL statement is not an error. If the data length of the aggregated data exceeds the maximum length of the LISTAGG result character string, the LISTAGG result will output a portion of the aggregated data and a truncated string suffix. The following shows examples.

Example:

  • aggregated data:'Bob|Mike|Nancy|Stephanie|Tom' ←28 bytes

  • maximum-length-of-the-LISTAGG-result-character-string specification: 15 bytes

  • Result of LISTAGG:Bob|Mike|... ← 12 bytes

    [Figure]

    Explanation:

    The LISTAGG result is output so that it fits within the maximum length of the LISTAGG result character string (15 bytes).

    Note that the truncated string suffix can be changed to any string from '...'. To change, specify truncated-string-suffix.

truncated-string-suffix:

Specify the string to be output at the end of the LISTAGG result if the data length of the aggregated data exceeds the maximum length of the LISTAGG result character string. Specify the truncated-string-suffix in character string literal format. For details about character string literals, see 6.3 Literals.

If truncated-string-suffix is omitted, '...' (real length 3-byte character data with three periods) is assumed to be the truncated string suffix.

The length of the truncated string suffix must be less than or equal to the value (in bytes) specified in maximum-length-of-the-LISTAGG-result-character-string.

Note

The specification of this option is called LISTAGG overflow behavior specification.

● WITHIN-group-specification
WITHIN-group-specification ::= WITHIN GROUP(ORDER BY sort-specification-list)

Specify for the WITHIN group specification the aggregation order (ascending or descending) of LISTAGG aggregated string. For details about the sort specification list, see 7.25 Sort specification list.

The following specification rules apply:

  • A null-value sort order specification is not permitted in the sort specification list in the WITHIN group specification.

  • No more than one sort specification is permitted in the sort specification list in the WITHIN group specification.

To Page Top

(3) Rules

  1. A maximum of 64 LISTAGG can be specified for a query specification.

  2. The data type of the value expression specified in the argument of LISTAGG determines the data type of the result of LISTAGG as shown in the following table.

    Table 7‒34: Relationship between the data type of the value expression specified as an argument for LISTAGG and the data type of the result of LISTAGG

    Data type of value expression

    Data type of LISTAGG result

    Numeric data

    INTEGER

    • When the maximum-length-of-the-LISTAGG-result-character-string (where t is the specified value) is specified

      For t = 3 to 32,000: VARCHAR(t)

      For t = 32,000,000: STRING

    • If the maximum-length-of-the-LISTAGG-result-character-string specification is omitted

      VARCHAR(1024)

    BIGINT

    SMALLINT

    DECIMAL

    NUMERIC

    DOUBLE PRECISION

    FLOAT

    REAL

    Character string data

    CHAR

    VARCHAR

    STRING

  3. The result of sequential execution of the following operations on the values derived from the LISTAGG argument value expression (the aggregated string of LISTAGG) is the input row of LISTAGG.

    • If the aggregated string contains a null value, the null value is eliminated.

    • If DISTINCT is specified, duplicate aggregated strings are eliminated and a single value is used.

    • Sort aggregated strings according to the sort-specification of the WITHIN-group-specification.

  4. If the number of LISTAGG input rows is 0, the execution result will be a null value.

  5. The following shows the LISTAGG execution result.

    In the example description, the aggregated data is 'Bob|Mike|Nancy|Stephanie|Tom' (28 bytes).

    ■If the data length of aggregated data ≤ maximum length of the LISTAGG result character string

    All of the aggregated data is output for the LISTAGG result.

    Example:

    Bob|Mike|Nancy|Stephanie|Tom

    ■If the data length of aggregated data > maximum length of the LISTAGG result character string

    • overflow-behavior with ERROR (omitted value) is specified

      The executed SQL statement will result in an error.

    • overflow-behavior with TRUNCATE is specified

      <Execution result 1>

      The result of LISTAGG will output a portion of the aggregated data truncated string suffix.

      Example:

      Example of SQL statement

      SELECT LISTAGG("C1",'|',24 ON OVERFLOW TRUNCATE '/*Overflow*/' WITHOUT COUNT)
         WITHIN GROUP (ORDER BY "C1") FROM "T1"

      The SQL statement above specifies 24 bytes for maximum-length-of-the-LISTAGG-result-character-string and '/*Overflow*/' for truncated-string-suffix.

      LISTAGG result

      [Figure]

      Explanation:

      • A portion of the aggregated data and the truncated string suffix are output to fit within the maximum length (24 bytes) of the LISTAGG result character string.

      • The last string of the portion of aggregated data output will be the LISTAGG separator character string ('|').

      • If there is one more aggregated string to output as follows, the data length will be 27 bytes, which exceeds the maximum length (24 bytes) of the LISTAGG result. Therefore, it is not output in this format.

        [Figure]

      <Execution result 2>

      Depending on the value specified for maximum-length-of-the-LISTAGG-result-character-string, LISTAGG may not output any aggregated strings in the result.

      Example:

      Example of SQL statement

      SELECT LISTAGG("C1",'|',15 ON OVERFLOW TRUNCATE '/*Overflow*/' WITHOUT COUNT)
         WITHIN GROUP (ORDER BY "C1") FROM "T1"

      The SQL statement above specifies 15 bytes for maximum-length-of-the-LISTAGG-result-character-string and '/*Overflow*/' for truncated-string-suffix.

      LISTAGG result

      [Figure]

      Explanation:

      • If the specified value of maximum-length-of-the-LISTAGG-result-character-string is too small, only the LISTAGG separator character string ('|') and truncated tail string ('/*Overflow*/') are output.

      • If one aggregated string is output as follows, the data length will be 16 bytes, which exceeds the maximum length of the LISTAGG result character string (15 bytes). Therefore, it is not output in this format.

        [Figure]

      <Execution result 3>

      maximum-length-of-the-LISTAGG-result-character-string < data-length-of-LISTAGG-separator-character-string + data-length-of-truncated-string-suffix, the LISTAGG result only outputs '...'.

      Example:

      Example of SQL statement

      SELECT LISTAGG("C1",'|',12 ON OVERFLOW TRUNCATE '/*Overflow*/' WITHOUT COUNT)
         WITHIN GROUP(ORDER BY "C1") FROM "T1"

      The SQL statement above specifies 12 bytes for maximum-length-of-the-LISTAGG-result-character-string and '/*Overflow*/' for truncated-string-suffix.

      LISTAGG result

      ...

      In the above example, the LISTAGG separator character string ('|') is 1 byte and the truncated string suffix ('/*Overflow*/') is 12 bytes. Thus, data-length-of-LISTAGG-separator-character-string + data-length-of-truncated-string-suffix is 13 bytes. Because the maximum length of the LISTAGG result character string (12 bytes) is exceeded, the LISTAGG result only outputs '...'.

To Page Top

(4) Examples

The following are examples of executing a SQL statement to search the sales table (SALES) shown below.

[Figure]

Example 1

For each customer ID (CID), determine the product ID (PID) of the purchased product. The result of LISTAGG (PID_LIST) outputs the commodity ID with the following conditions

  • Concatenate product IDs (PIDs) in unit price (PRICE) order.

  • Separate each product ID with '|'.

SELECT "CID",
       LISTAGG("PID",'|') WITHIN GROUP (ORDER BY "PRICE" ASC) AS "PID_LIST"
  FROM "SALES"
    GROUP BY "CID"

The above SELECT statement specifies the following (underlined above).

  • LISTAGG-separator-character-string with '|'.

  • WITHIN-group-specification, the sort-specification-list is specified to concatenate product IDs in unit price (PRICE) order.

Execution result example

[Figure]

Example 2 (for eliminating duplicate values from the LISTAGG aggregated string)

For each customer ID (CID), determine the product ID (PID) of the purchased product. The result of LISTAGG (PID_LIST) outputs the commodity ID with the following conditions

  • Concatenate product IDs (PIDs) in unit price (PRICE) order.

  • Separate each product ID with '|'.

  • Eliminate duplicate product IDs.*

*: This is a condition that differs from Example 1.

SELECT "CID",
       LISTAGG(DISTINCT "PID",'|') WITHIN GROUP (ORDER BY "PRICE" ASC) AS "PID_LIST"
  FROM "SALES"
    GROUP BY "CID"

In the SELECT statement above, a DISTINCT is specified (underlined above) that eliminates duplicate values from the LISTAGG aggregated string.

Execution result example

[Figure]

Example 3 (outputting '...' for the truncated string suffix)

For each customer ID (CID), determine the product ID (PID) of the purchased product. The result of LISTAGG (PID_LIST) outputs the commodity ID with the following conditions

  • Concatenate product IDs (PIDs) in unit price (PRICE) order.

  • Separate each product ID with '|'.

  • The maximum length of the LISTAGG result character string is 20 bytes (VARCHAR(20)).*

  • If the data length of the aggregated data exceeds the maximum length of the LISTAGG result character-string, a truncated string suffix ('...') is output.*

*: This is a condition that differs from Example 1.

SELECT "CID",
       LISTAGG("PID",'|',20 ON OVERFLOW TRUNCATE WITHOUT COUNT)
         WITHIN GROUP (ORDER BY "PRICE" ASC) AS "PID_LIST"
  FROM "SALES"
    GROUP BY "CID"

The above SELECT statement specifies the following (underlined above).

  • The maximum-length-of-the-LISTAGG-result-character-string is specified to 20 bytes.

  • overflow-behavior specifies that if the data length of the aggregated data exceeds the maximum length of the LISTAGG result character string, a truncated string suffix of '...' (abbreviated value) is specified to be output.

Execution result example

[Figure]

[Explanation]

  • In the PID_LIST (the result of LISTAGG) for the row with the CID (customer ID) of 'C0001', the aggregated data is output in its entirety because the data length (17 bytes) is less than the maximum length of the LISTAGG result character string (20 bytes).

  • In the PID_LIST (the result of LISTAGG) for the row with the CID (customer ID) of 'C0002', part of the aggregated data and the end of the truncated string suffix ('...') is output because the data length (23 bytes) exceeds the maximum length of the LISTAGG result character string (20 bytes).

Example 4 (outputting an arbitrary string in the truncated string suffix)

For each customer ID (CID), determine the product ID (PID) of the purchased product. The result of LISTAGG (PID_LIST) outputs the commodity ID with the following conditions

  • Concatenate product IDs (PIDs) in unit price (PRICE) order.

  • Separate each product ID with '|'.

  • The maximum length of the LISTAGG result character string is 20 bytes (VARCHAR(20)).

  • If the data length of the aggregated data exceeds the maximum length of the LISTAGG result character-string, a truncated string suffix ('/*Overflow*/') is output.*

*: This is a condition that differs from Example 3.

SELECT "CID",
       LISTAGG("PID",'|',20 ON OVERFLOW TRUNCATE '/*Overflow*/' WITHOUT COUNT)
         WITHIN GROUP (ORDER BY "PRICE" ASC) AS "PID_LIST"
  FROM "SALES"
    GROUP BY "CID"

The above SELECT statement specifies the following (underlined above).

  • The maximum-length-of-the-LISTAGG-result-character-string is specified to 20 bytes.

  • overflow-behavior specifies that if the data length of the aggregated data exceeds the maximum length of the LISTAGG result character string, a truncated string suffix of '/*Overflow*/' is specified to be output.

Execution result example

[Figure]

[Explanation]

  • In the PID_LIST (the result of LISTAGG) for the row with the CID (customer ID) of 'C0001', the aggregated data is output in its entirety because the data length (17 bytes) is less than the maximum length of the LISTAGG result character string (20 bytes).

  • In the PID_LIST (the result of LISTAGG) for the row with the CID (customer ID) of 'C0002', part of the aggregated data and the end of the truncated string suffix (/*Overflow*/') is output because the data length (23 bytes) exceeds the maximum length of the LISTAGG result character string (20 bytes).

Example 5 (if the SQL statement is an error when the data length of the aggregated data exceeds the maximum length of the LISTAGG result character string)

For each customer ID (CID), determine the product ID (PID) of the purchased product. The result of LISTAGG (PID_LIST) outputs the commodity ID with the following conditions

  • Concatenate product IDs (PIDs) in unit price (PRICE) order.

  • Separate each product ID with '|'.

  • The maximum length of the LISTAGG result character string is 20 bytes (VARCHAR(20)).

  • If the data length of the aggregated data exceeds the maximum length of the LISTAGG result character-string, the SQL statement is an error.*

*: This condition differ from Example 3 and Example 4.

SELECT "CID",
       LISTAGG("PID",'|',20 ON OVERFLOW ERROR)
         WITHIN GROUP (ORDER BY "PRICE" ASC) AS "PID_LIST"
  FROM "SALES"
    GROUP BY "CID"

The above SELECT statement specifies the following (underlined above).

  • The maximum-length-of-the-LISTAGG-result-character-string is specified to 20 bytes.

  • overflow-behavior specifies that if the data length of the aggregated data exceeds the maximum length of the LISTAGG result character string, the SQL statement is an error.

Execution result example

The SQL statement executed will fail because there is a row with aggregated data 'P0013|P0008|P0010|P0016' (23 bytes) and the data length of the aggregated data exceeds the maximum length of the LISTAGG result character string.

To Page Top

(5) Notes

  1. If the sort key specified in WITHIN-group-specification has the same value, the aggregation order of the aggregated string in LISTAGG may differ even if the same SQL statement is executed. The following shows examples.

    Example:

    Tables to be searched

    [Figure]

    SQL statement to be executed

    SELECT LISTAGG("C1",'|') WITHIN GROUP(ORDER BY "C2") AS "Name" FROM "T1"

    Execution result example 1

    Tom|Mike|Stephanie|Flora|Nancy

    Execution result example 2

    Tom|Stephanie|Mike|Flora|Nancy

    Because Mike and Stephanie have the same sort key value, the execution result (aggregation order of aggregated string in LISTAGG) may differ as in the example above, even if the same SQL statement is executed.

  2. If maximum-length-of-the-LISTAGG-result-character-string is omitted, the maximum length of the LISTAGG result character string is assumed to be 1,024 bytes. If the data length of the aggregated data is significantly smaller than 1,024 bytes, the LISTAGG process may use more resources than necessary, resulting in errors such as exceeding the maximum row length of the working table. If such an error occurs, it is recommended that you specify a value for the maximum-length-of-the-LISTAGG-result-character-string that is calculated using the following formula.

    (data_len + data-length-of-LISTAGG-separator-character-string) × number-of-LISTAGG-aggregated-strings

    data_len: Substitute the following value according to the data type of the value expression specified in the argument of LISTAGG.

    Table 7‒35: Value to be substituted for data_len

    Data type of value expression

    Value to be substituted for data_len

    Example

    CHAR(n)

    n

    None

    VARCHAR

    Substitutes the maximum real length of the LISTAGG aggregated string.

    If the aggregated strings of LISTAGG are 'AB', 'ABC', and 'ABCD', data_len is substituted to 4.

    STRING

    BIGINT

    Substitutes the maximum length of the string # resulting from the conversion of the numeric data of the LISTAGG aggregated string into character string data.

    If the result of converting BIGINT, INTEGER, or SMALLINT type data to character string data is '12345', '123456', or '-123456', 7 is substituted to data_len.

    INTEGER

    SMALLINT

    DECIMAL

    If the result of converting DECIMAL, or NUMERIC data to character string data is '1.20', '12.34', or '-12.34', 6 is substituted to data_len.

    NUMERIC

    DOUBLE PRECISION

    If the result of converting DOUBLE PRECISION, FLOAT, or REAL type data to character string data is '1.23E45', '-1.23E45', or '1.23E-45', 8 is substituted to data_len.

    FLOAT

    REAL
    #

    The rules when converting numeric data to character string data follow the rules for converting numeric data to character string data with the scalar function CONVERT (when no number format is specified). For details about the rules when converting numeric data to character string data with the scalar function CONVERT, see (c) Rules for converting to character string data in (5) Rules in 8.13.5 CONVERT.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2026, Hitachi, Ltd.