Хотя такая группировка противоречит правилу первой нормальной формы (атомарность), в ORACLE предусмотрена функция для подобного преобразования.
Для примера, есть таблица CALENDAR, которая содержит номера кварталов и названия месяцев.
select distinct C.QUARTER_ID, C.MONTH_NAME_EN, C.MONTH_ID from calendar c
QUARTER_ID | MONTH_NAME_EN | MONTH_ID |
01 | january | 01 |
01 | february | 02 |
01 | march | 03 |
02 | april | 04 |
02 | may | 05 |
02 | june | 06 |
03 | july | 07 |
03 | august | 08 |
03 | september | 09 |
04 | october | 10 |
04 | november | 11 |
04 | december | 12 |
Нужно записать месяцы каждого квартала в одну строку.
Функция WM_CONCAT агрегирует записи в одну строку. Функция принадлежит пользователю WMSYS. В зависимости от конфигурации базы этого пользователя (а значит и функции) может и не быть. Второй недостаток - в качестве разделителя по умолчанию значится запятая, но с этим можно успешно бороться посредством REPLACE. В приведенном примере с помощью REPLACE к запятой добавлен пробел для придания тексту более читабельного вида.
select C.QUARTER_ID, replace(wm_concat(C.MONTH_NAME_EN), ',', ', ') as months from (select distinct C.QUARTER_ID QUARTER_ID, C.MONTH_NAME_EN MONTH_NAME_EN, C.MONTH_ID MONTH_ID from calendar c) c group by C.QUARTER_ID order by 1Как видно из результата в WM_CONCAT сортировка также не предусмотрена :
QUARTER_ID | MONTHS |
01 | january, february, march |
02 | june, may, april |
03 | august, september, july |
04 | november, december, october |
В ORACLE 11g Release 2 появилась функция LISTAGG, которая позволяет задавать разделитель, производить сортировку внутри строки.
select C.QUARTER_ID, LISTAGG(C.MONTH_NAME_EN, ', ') WITHIN GROUP (ORDER BY C.MONTH_ID) AS MONTHS from (select distinct C.QUARTER_ID QUARTER_ID, C.MONTH_NAME_EN MONTH_NAME_EN, C.MONTH_ID MONTH_ID from calendar c) c group by C.QUARTER_ID order by 1Результат:
QUARTER_ID | MONTHS |
01 | january, february, march |
02 | april, may, june |
03 | july, august, september |
04 | october, november, december |
Функция LISTAGG возвращает данные в формате varchar что накладывает соответствующие ограничения на результат.