Объединение значений столбца

Функция

Объединяет выбираемые значения столбца в одно значение (т.е. вертикальное представление значений столбца возвращает в виде горизонтального представления).

Схема выполнения функции представлена на рисунке 2.

Схема объединения значений столбца
Рисунок 2. Схема объединения значений столбца

Спецификация
   
< разделитель >::=
< длина возвращаемого значения >::=
целочисленное положительное значение
Синтаксические правила
  1. Аргумент < выражение > может быть любым выражением, кроме типа данных BOOLEAN, BLOB, EXTFILE. NULL-значения игнорируются.

    select LISTAGG(rtrim(c), '': ') within group (order by c) from tst;

    Примечание

    Для выражения типа BOOLEAN можно вначале привести значение выражения к типу данных int, например, select LISTAGG(cast st as int) from tab_w1;

  2. Аргумент < разделитель > задает символьный литерал, который разделяет значения в результирующей записи выборки данных. Аргумент необязательный, по умолчанию он равен NULL-значению, вследствие чего результирующая строка будет без разделителей.

    create or replace table tphones(dept int, name varchar(20), phone varchar(30));
    insert into tphones(dept, name, phone) values (1,'Петров','1-00-01');
    insert into tphones(dept, name, phone) values (1,'Иванов','1-11-01');
    insert into tphones(dept, name, phone) values (1,'Иванов','1-11-03');
    insert into tphones(dept, name, phone) values (2,'Сидоров','1-33-03');
    insert into tphones(dept, name, phone) values (2,'Сидоров','1-33-01');
    а) с разделителем
    select LISTAGG(phone,'; ', 50) within group (order by phone) phones_phone 
      from tphones;
    
    |1-00-01; 1-11-01; 1-11-03; 1-33-01; 1-33-03|
    
    
    б) без разделителя
    select LISTAGG(phone) within group (order by phone) phones_phone
      from tphones;
     PHONES_PHONE
     ------------
    |1-00-011-11-011-11-031-33-011-33-03|

    Примечание

    Разделитель вставляется после первого значения в результирующей записи выборки данных.

  3. Аргумент < длина возвращаемого значения > определяет допустимую для данного запроса длину результирующей записи выборки данных. Аргумент необязательный, по умолчанию длина равна 4000 символов (т.е. функция возвращает значение типа VARCHAR(4000)).

    Примечание

    Если при формировании результирующей записи выборки данных ее длина начнет превышать < длину возвращаемого значения >, возвращается код завершения 1133 «Результат строковой конкатенации слишком велик».

  4. Одновременное использование < OVER-спецификации > и < WITHIN GROUP-спецификации > не разрешается.

Общие правила
  1. Функция LISTAGG упорядочивает данные, объединенные в группы, после чего соединяет значения < выражение >:

    • как одиночная агрегатная функция, LISTAGG обрабатывает все записи выборки данных и возвращает одно значение;

      Тестовые данные.
      create or replace table tphones(dept int, name varchar(20), phone varchar(30));
      insert into tphones(dept, name, phone) values (1,'Петров','1-00-01');
      insert into tphones(dept, name, phone) values (1,'Иванов','1-11-01');
      insert into tphones(dept, name, phone) values (1,'Иванов','1-11-03');
      insert into tphones(dept, name, phone) values (1,'Иванов','1-11-02');
      insert into tphones(dept, name, phone) values (1,'Иванов','1-11-05');
      insert into tphones(dept, name, phone) values (2,'Сидоров','1-33-03');
      insert into tphones(dept, name, phone) values (2,'Сидоров','1-33-01');
      insert into tphones(dept, name, phone) values (2,'Сидоров','1-33-02');
      
      Выдать одной строкой отсортированный список всех номеров телефонов, внесенных в таблицу tphones:
      select LISTAGG(phone, '; ', 80) within group (order by phone) phones_phone
        from tphones;
      
      Результат выполнения запроса:
      
       PHONES_PHONE
       ------------
      |1-00-01; 1-11-01; 1-11-02; 1-11-03; 1-11-05; 1-33-01; 1-33-02; 1-33-03      |
    • как групповая агрегатная функция, LISTAGG обрабатывает и возвращает данные для каждой группы, указанной в < GROUP BY-спецификации >;

      Выдать для каждого человека одной строкой отсортированный список его телефонов:
      select name, LISTAGG(phone, '; ', 50) within group (order by phone) phones_phone
       from tphones group by name;
      
      Результат выполнения запроса:
      
       NAME                 PHONES_PHONE
       ----                 ------------
      |Иванов              |1-11-01; 1-11-02; 1-11-03; 1-11-05     |
      |Петров              |1-00-01                                |
      |Сидоров             |1-33-01; 1-33-02; 1-33-03              |
    • как аналитическая функция, LISTAGG обрабатывает данные, разбитые на блоки, задаваемые < OVER-спецификацией >.

      Выдать для каждой записи таблицы (относящейся к человеку) все отсортированные телефоны этого человека:
      select distinct name, LISTAGG(phone, '; ', 40) over (partition by name order by phone) phones from tphones;
      
      Результат выполнения запроса:
      
       NAME                 PHONES
       ----                 ------
      |Иванов              |1-11-01; 1-11-02; 1-11-03; 1-11-05      |
      |Петров              |1-00-01                                 |
      |Сидоров             |1-33-01; 1-33-02; 1-33-03               |