Вывести значения через запятую oracle

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

LISTAGG ФУНКЦИЯ

В этом учебном пособии вы узнаете, как использовать Oracle/PLSQL функцию LISTAGG с синтаксисом и примерами.

Описание

Oracle/PLSQL функция LISTAGG объединяет значения measure_column для каждой группы на основе order_by_clause .

Синтаксис

Синтаксис Oracle/PLSQL функции LISTAGG:

Параметры или аргументы

measure_column столбец, значения которого вы хотите объединить вместе в наборе результатов. Нулевые значения в measure_column игнорируются.

delimiter не является обязательным. Это разделитель используется при разделении значений measure_column при выводе результатов.

order_by_clause определяет порядок связанных значении (т.е.: measure_column ) которые возвращаются.

Функция LISTAGG возвращает string значение.

Применение

Функцию LISTAGG можно использовать в следующих версиях Oracle/PLSQL:

• Oracle 12c, Oracle 11g Release 2

Пример

Функция LISTAGG может быть использована в Oracle/PLSQL.

Так как это более сложная функция, то чтобы ее понять, давайте посмотрим на примере, который включает данные, которые функция демонстрирует на выходе.

Если у вас есть таблица products со следующими данными:

PRODUCT_ID	PRODUCT_NAME
1001	Bananas
1002	Apples
1003	Pears
1004	Oranges

И вы выполните следующий SQL запрос, используя функцию LISTAGG:

Источник

APPS-ORACLE.RU

Функция LISTAGG (объединение строк)

LISTAGG упорядочивает данные, объединенные в группы конструкцией ORDER BY, затем соединяет указаный столбец measure_expr.

1) Как одиночная агрегатная функция, LISTAGG обрабатывает все строки и возвращает одно значение.

2) Как групповая агрегатная функция, LISTAGG обрабатывает и возвращает данные для каждой группы определенной в GROUP BY.

3) Как аналитическая функция, LISTAGG обрабатывает данные разбитые на блоки, задаваемые одним или несколькими выражениями query_partition_clause.

Пример агрегатной функции

Пример аналитической функции

Похожие записи:

Жаль, что LISTAGG не позволяет использовать DISTINCT, чтобы убрать дубли перед агрегацией-конкатенацией (как в самописанной STRAGG). Но, несомненный плюс, это возможность сортировки элементов в конкатенированной строке (чего не было в STRAGG).

@Fima
Но всегда есть возможность из полученной строки удалить дубликаты.

Если использовать listagg без сортировки, то регулярное выражение будет сложнее

p.s.: да я ответил сам себе почти через два года, потому что забыл как реализовывал тогда и потратил время на решение.

А можно как-то ограничить список строк для группировки в listagg ? Проблема такая: обрабатывается 100 строк, и общая строка listagg() получается больше 4000 символов. ХОтелось быть ограничить в этом случае число строк 30ью, например. Это реально?

@Алексей
К сожалению, мне такой способ неизвестен. В этом случае надо использовать альтернативные методы.

@Алексей
Можно, например, обрезать финальный список строковой функцией до нужных размеров.

@Ярослав
А пример есть? А то я таких не знаю способов )

@Алексей
Если вас устроит CLOB, то можно не ограничивать. Я извернулся вот таким способом, потому как ограничивать и обрезать было нельзя:

DISTINCT
select id, LISTAGG(name, ‘, ‘) WITHIN GROUP (order by name) as lic_list
from
(
select
distinct ld.obj_id as id, dl.short_name as name
from
dic_license dl,
license_det ld
where
dl.id = ld.dic_license_id
)
group by id

@MErdock
У меня почему то после такой регулярки ничего не просходит со строкой (.

@Fima
А почему нельзя так-

select deptno, LISTAGG(t.ename, ‘, ‘) WITHIN GROUP (order by t.empno) as ename_list

from (select distinct deptno,ename from scott.emp ) t

@Mr.Grizz
а не потому, что уникальный id_obj не позволяет ?

@Алексей
У аналитической функции можно задать окно: Range/Rows
Окно задаётся после «order by»
Пример, listagg (поле_3,’,’) withing group (order by поле_2 rows 9 preceding) over (partition by поле_1)
таким образом должны захватиться текущая строка выборки и 9 предыдущих.

@l___RUS___l
закосячил. Listagg не работает с окном.
Необходимо использовать str_agg

у меня получилось сделать нормально LISTAGG в группировке и без повторений. Фишка в том, что нужно в подселекте сделать основную группировку, а во внешнем селекте сделать группировку с листаг — к этому моменту в подзапросе уже будут сгруппированы\удалены повторения и листагг их пропустит.

Ещё момент с двумя листагг в одном запросе — может потребоваться ещё подселект. для одного листагг — один селект с группировкой.

AlexB :
у меня получилось сделать нормально LISTAGG в группировке и без повторений.

SELECT LISTAGG(APPLICID, ‘,’) WITHIN GROUP (ORDER BY APPLICID) into er_desc FROM RTDM_SAS_LOG rsa
WHERE rsa.EVENT_CODE NOT IN (‘0′,’4′,’2203′,’2205′)
AND ROUND(SYSTIMESTAMP,’HH’) — ROUND(rsa.TIME_STAMP,’HH’) Eddy

Пример как на Орасле 11 получить несколько LISTAGG в одной группировке и с distinct к каждому полю.
Работает со скоростью wm_concat.
Стащено со stackoverflow.com и проверено на коде нескольких вьюх…

Внутренняя группировка строит колекции и делает агрегаты с групой.
Внешний запрос выполняет Listagg подзапросом к коллекции.
Получаем и distinct и нет ограничений на количество Listagg в одной групировке.

with test_data as
(
select ‘A’ as col1, ‘T_a1’ as col2, ‘123’ as col3 from dual
union select ‘A’, ‘T_a1’, ‘456’ from dual
union select ‘A’, ‘T_a1’, ‘789’ from dual
union select ‘A’, ‘T_a2’, ‘123’ from dual
union select ‘A’, ‘T_a2’, ‘456’ from dual
union select ‘A’, ‘T_a2’, ‘111’ from dual
union select ‘A’, ‘T_a3’, ‘999’ from dual
union select ‘B’, ‘T_a1’, ‘123’ from dual
union select ‘B’, ‘T_b1’, ‘740’ from dual
union select ‘B’, ‘T_b1’, ‘846’ from dual
)
select col1
, (select listagg(column_value, ‘,’) within group (order by column_value desc) from table(collect_col2)) as col2s
, (select listagg(column_value, ‘,’) within group (order by column_value desc) from table(collect_col3)) as col3s
from
(
select col1,
collect(distinct col2) as collect_col2,
collect(distinct col3) as collect_col3
from test_data
group by col1
);

Источник

Как обработать столбец, содержащий значения строки, разделенные запятыми или диапазон, в Oracle

Используя БД Oracle 12c, у меня есть следующий пример данных таблицы, в котором мне нужна помощь с использованием SQL и PL / SQL.

Таблица данных выглядит следующим образом:

Используя приведенные выше данные в таблице my_data , как лучше всего обработать этот ITEM_LOC, так как мне нужно использовать значения в этом столбце в качестве отдельного значения, т.е.

0,1 означает, что SQL должен вернуть либо 0, либо 1, либо

Диапазон значений, т.е.

0-48 означает, что SQL должен возвращать значение от 0 до 48.

Возвращенные значения для обоих сценариев должны начинаться с самого низкого до самого высокого и не могут быть повторно использованы после обработки.

Исходя из вышеизложенного, было бы здорово иметь функцию, которая принимает идентификатор и возвращает отдельное значение из ITEM_LOC, которое не использовалось, на основании моего описания выше. Это может быть строковое значение через запятую или строковое значение диапазона.

Желаемый результат для может быть 7. Для этого ITEM_LOC = 7 не может быть использован снова.

Желаемый результат для может быть 31. Для этого ITEM_LOC = 31 не может быть использован снова.

Для данных ITEM_LOC, которые нельзя было использовать снова, для этого идентификатора, я рассматриваю удерживание другой таблицы для хранения этой или, возможно, разделения всех данных в отдельные строки с новым столбцом с именем VALUE_USED.

2 ответа

Как уже говорили другие, плохая идея хранить данные таким способом. Скорее всего, у вас вход такой, и вам, вероятно, потребуется отобразить данные, подобные этим, но вам не нужно хранить данные так, как они вводятся. или отображается.

Я собираюсь хранить данные в виде отдельных LOC элементов на основе входных данных. Я предполагаю, что данные содержат только целые числа, разделенные запятыми, или пары целых чисел, разделенные дефисом. Пробелы игнорируются. Список через запятую не должен быть в любом порядке. В парах, если левое целое больше правого, я не возвращаю элемент LOC .

Теперь, чтобы «использовать» элемент, я просто удаляю его из таблицы:

Чтобы вернуть данные в том же формате, в котором они были введены, используйте MATCH_RECOGNIZE :

Обратите внимание, что выходные данные здесь не будут точно такими же, как входные, потому что любые последовательные целые числа будут сжаты в пару.

Этот запрос показывает, как извлечь список значений ITEM_LOC на основе того, являются ли они разделенными запятыми (что означает «принимать именно эти значения») или разделенными дефисом (что означает «найти все значения между начальной и конечной точкой») , Я немного изменил ваши образцы данных (не хотелось отображать

50 значений, если 5 из них сделали свою работу).

• Строки # 1 — 6 представляют данные образца.
• первый select (строки # 7 — 15) разбивает значения через запятую на строки
• второй select (строки # 17 — 26) использует иерархический запрос, который добавляет 1 к начальному значению до конечного значения элемента.

Я не знаю, что вы имели в виду, говоря, что «item_loc не может быть использован снова». Использовали где ? Если вы используете вышеупомянутый запрос, например, в цикле курсора FOR , тогда да — эти значения будут использоваться только один раз, поскольку каждая итерация цикла выбирает следующее значение item_loc .

Источник

Недокумментированная функция Oracle WM_CONCAT

В Oracle можно встретить недокумментированные функции, которые свою очередь весьма полезны. Хотелось бы рассмотреть одну из них используемую и появивщуюся в Oracle версиях 10g. В Oracle версиях выше 11g R2 есть уже докумментированная функция выполняющая тот же функционал. Наверное, интересно что это за функционал. Давайте рассмотрим стандартную задачу. У нас есть департамент и сотрудники соотвественно прикрепленные за этим департаментом. Нам дали задачу вывести все департаменты их описание и имена сотрудников работающих в нем. При этом строк должно быть равно количеству департамента. Естесственно у нас появляется вопрос, ведь сотрудники и департамент имеет соотношение один ко многим, плюс к тому же мы не сможем точно знать количество сотрудников в каждый момент, и заводить столбец для каждого сотрудника пустое расстачительство место, которое не имеет смысла. Одним из решение которое выглядит весьма красивым — есть перечисленние имена всех сотрудников через запятую в одном столбце. Как же решить это используя только SQL не прибегая к программированияю на PL/SQL.

Вот в этом случаи к нам на помощь и приходит WM_CONCAT, которая поможет соединить строки с наименьшими усилиями.

К примеру есть таблица по которой запрос ввиде

SELECT deptno, ename AS employees
FROM scott.emp

возвращает следующие строки:

20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER

Мы хотим отобразить имена сотрудников через запятую согласно департамента в котором трудится этот сотрудник. Написав и выполнив запрос:

SELECT deptno, wm_concat (ename) AS employees
FROM scott.emp
GROUP BY deptno;

10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Да впечатляет, легкость решение не простой задачи. Но здесь есть оговорки — Имена сотрудников можно перечислять только через запятую. Если количество сотрудников в одном департаменте составит больше определенного количество, то может вернутся ошибка. В некоторых релизях Oracle версиях 10g возможно данная функция будет работать иначе — это будет естественное поведение недокументированной функции, на то она и недокументированная.

В Oracle версиях выше 11g R2 появилась функция LISTAGG, в которой место запятой можно указать тот разделитель который вам хочется.
пример запроса используя LISTAGG:

Источник

вывод списка через запятую

Перечисление записи через запятую
Вывести список сотрудников компании, имеющих коллег с таким же идентификатором должности и.

Вывод нескольких строк через запятую
Мне нужно получить данные строки 1,2 3,4 5,6 из таблицы id 1 2 3 4

Значения через запятую
Подскажите, пожалуйста, как такое можно сделать? MS Server 2008 в таблице t1 есть колонка idType.

Данные через запятую
Здравствуйте, ув.-мые формучанины в VBA я слаб, появилась необходимость данные из запроса.

Получение параметров через запятую
Здравствуйте. В хранимую процедуру передаётся строка, на пример: «0,1,2,546,101,6,9» Нужно эту.

Выборка по перечислению через запятую
Всем привет, как то можно стандатными средствами сделать данную выборку? SELECT id FROM `images`.

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

Значения столбца в строку через запятую
есть столбец mail в таблице org select mail from org aaa@nnn.ru kkk@jjj.ru kkk@fff.ru .

Значения через запятую в отдельных строчках
Всем привет:) В общем есть таблица , в которой значения перечслены через запятую. Как можно.

Вывести данные которые через запятую
В таблице есть данные, которые через запятую, как их вывести в столбик. Например SELECT.

Источник