Превращаем столбцы в строчки LISTAGG

добавлено: 13 май 13
понравилось:0
просмотров: 33739
комментов: 6

теги:

Автор: Myp3_u_K

Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA
-- Превращаем столбцы в строчки
-- функция LISTAGG
-
Описание функции listagg
согласно переводу Oracle Reference Database listagg может использоваться
1) Как одиночная агрегатная функция, LISTAGG обрабатывает все строки и возвращает одно значение.
2) Как групповая агрегатная функция, LISTAGG обрабатывает и возвращает данные для каждой группы определенной в GROUP BY.
3) Как аналитическая функция, LISTAGG обрабатывает данные разбитые на блоки, задаваемые одним или несколькими выражениями query_partition_clause.
давайте рассмотрим самый интересный пример:
создадим таблицу следующего вида
имя клиента , номер телефона клиента

по ТЗ у каждого клиента может быть несколько телефонных номеров

подготовим тестовые данные
create table cl_phones(tname varchar2(100), phone varchar2(30));
insert into cl_phones(tname,phone) values ('Андрей','867-843-25');
insert into cl_phones(tname,phone) values ('Андрей','830-044-35');
insert into cl_phones(tname,phone) values ('Андрей','530-055-35');
insert into cl_phones(tname,phone) values ('Максим','530-055-35');
insert into cl_phones(tname,phone) values ('Максим','555-011-35');
insert into cl_phones(tname,phone) values ('Ваня','530-055-31');
insert into cl_phones(tname,phone) values ('Ваня','531-051-32');
insert into cl_phones(tname,phone) values ('Ваня','532-052-33');
insert into cl_phones(tname,phone) values ('Ваня','533-053-35');


-- давайте напишем запрос который бы выводил имя , телефоны в строку с разделителем
SELECT
   tname,
   LISTAGG(phone, ' ; ') WITHIN GROUP (ORDER BY phone) phonestr
FROM cl_phones
GROUP BY tname


результат
tname phonestr
Андрей 530-055-35 ; 830-044-35 ; 867-843-25
Ваня 530-055-31 ; 531-051-32 ; 532-052-33 ; 533-053-35
Максим 530-055-35 ; 555-011-35


необходимо отметить что функция LISTAGG появилась только в версии 11.2
Чалышев М.М www.orasource.ru

Комментарии


  • хорошо. Для xml использую написал себе listagg_unique, делает тоже самое, но фильтрует повторяющиеся значения.

  • Спасибо большое за пример.
    Было бы ещё неплохо добавить пару примеров более сложных ( с OVER(PARTITION BY)).

  • При использовании функции наткнулся на ограничение по длине возвращаемого значения "ORA-01489: result of string concatenation is too long"

    SELECT LISTAGG(table_name, ', ') WITHIN GROUP (ORDER BY table_name) names
    FROM user_tables

  • да такое возможно , если есть смешивание типов или получается слишком длинная строка

  • к сожалению не используется с окном

  • 28 июля 2017, 14:16 Ерошевич Павел

    Для более ранних версий можно использовать недокументированную функцию WM_CONCAT



Необходимо войти на сайт, чтобы оставлять комментарии