Как можно объединить несколько строк в список с разделителями-запятыми в Oracle? [Дубликат]

92

У меня есть простой запрос:

select * from countries

со следующими результатами:

country_name
------------
Albania
Andorra
Antigua
.....

Я хотел бы вернуть результаты в одну строку, так вот:

Albania, Andorra, Antigua, ...

Конечно, я могу написать PL/SQL-функцию для выполнения задания (я уже делал это в Oracle 10g), но есть ли лучшее, желательно не-Oracle-специфическое решение (или может быть встроенной функцией) для этой задачи?

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

Мой вопрос основан на аналогичном вопросе на SQL Server 2005.

UPDATE: Моя функция выглядит так:

CREATE OR REPLACE FUNCTION APPEND_FIELD (sqlstr in varchar2, sep in varchar2 ) return varchar2 is
ret varchar2(4000) := '';
TYPE cur_typ IS REF CURSOR;
rec cur_typ;
field varchar2(4000);
begin
     OPEN rec FOR sqlstr;
     LOOP
         FETCH rec INTO field;
         EXIT WHEN rec%NOTFOUND;
         ret := ret || field || sep;
     END LOOP;
     if length(ret) = 0 then
          RETURN '';
     else
          RETURN substr(ret,1,length(ret)-length(sep));
     end if;
end;
Теги:
pivot
concatenation
string-aggregation

11 ответов

61
Лучший ответ

Вот простой способ без stragg или создания функции.

create table countries ( country_name varchar2 (100));

insert into countries values ('Albania');

insert into countries values ('Andorra');

insert into countries values ('Antigua');


SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
      FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
                   COUNT (*) OVER () cnt
              FROM countries)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

CSV                                                                             
--------------------------
Albania,Andorra,Antigua                                                         

1 row selected.

Как отмечали другие, если вы на 11 г R2 или выше, теперь вы можете использовать listagg, который намного проще.

select listagg(country_name,', ') within group(order by country_name) csv
  from countries;

CSV                                                                             
--------------------------
Albania, Andorra, Antigua

1 row selected.
Поделиться
  • 0
    Хорошее короткое решение, но пара опечаток испортила его. Эта строка должна выглядеть следующим образом: FROM (ВЫБЕРИТЕ название страны, ROW_NUMBER () OVER (ORDER BY название страны) rn,
  • 2
    Предложение JoshL об использовании функции LISTAGG очень предпочтительно для тех, кто использует 11.2 или новее.
Показать ещё 3 комментария
106

Функция WM_CONCAT (если она включена в вашу базу данных, pre Oracle 11.2) или LISTAGG (начиная с Oracle 11.2), должна хорошо сделать трюк. Например, это получает список имен таблиц в вашей схеме с разделителями-запятыми:

select listagg(table_name, ', ') within group (order by table_name) 
  from user_tables;

или

select wm_concat(table_name) 
  from user_tables;

Подробнее/параметры

Ссылка на документацию

Поделиться
Показать ещё 2 комментария
18

Для Oracle вы можете использовать LISTAGG

Поделиться
  • 6
    В Oracle 11.2, как указал JoshL.
16

вы можете попробовать этот запрос.

select listagg(country_name,',') within group (order by country_name) cnt 
from countries;
Поделиться
  • 0
    Листагг был представлен в Oracle 11g Release 2.
  • 1
    это не сработает, если будет слишком много столбцов.
15

Вы также можете использовать это:

SELECT RTRIM (
          XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
          ',')
          country_name
  FROM countries;
Поделиться
  • 0
    Спасибо! Это работает в Oracle 10g.
4

Самый быстрый способ использовать функцию сбора данных Oracle.

Вы также можете сделать это:

select *
  2    from (
  3  select deptno,
  4         case when row_number() over (partition by deptno order by ename)=1
  5             then stragg(ename) over
  6                  (partition by deptno
  7                       order by ename
  8                         rows between unbounded preceding
  9                                  and unbounded following)
 10         end enames
 11    from emp
 12         )
 13   where enames is not null

Посетите сайт ask tom и выполните поиск по 'stragg' или 'string concatenation'. Много Примеры. Существует также не документированная функция оракула для достижения ваших потребностей.

Поделиться
2

В этом примере мы создаем функцию, чтобы принести разграниченный список разграниченных строк контировки на уровне строк в одном поле для запроса уровня заголовка:

 FUNCTION getHoldReasonsByInvoiceId (p_InvoiceId IN NUMBER) RETURN VARCHAR2

  IS

  v_HoldReasons   VARCHAR2 (1000);

  v_Count         NUMBER := 0;

  CURSOR v_HoldsCusror (p2_InvoiceId IN NUMBER)
   IS
     SELECT DISTINCT hold_reason
       FROM ap.AP_HOLDS_ALL APH
      WHERE status_flag NOT IN ('R') AND invoice_id = p2_InvoiceId;
BEGIN

  v_HoldReasons := ' ';

  FOR rHR IN v_HoldsCusror (p_InvoiceId)
  LOOP
     v_Count := v_COunt + 1;

     IF (v_Count = 1)
     THEN
        v_HoldReasons := rHR.hold_reason;
     ELSE
        v_HoldReasons := v_HoldReasons || ', ' || rHR.hold_reason;
     END IF;
  END LOOP;

  RETURN v_HoldReasons;
END;
Поделиться
2

Мне нужна была аналогичная вещь и нашел следующее решение.

select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from
Поделиться
  • 1
    Хотя это работает, я не рекомендую это решение никому. Я видел команду обновления для таблицы с только 80 000 строк, используя это решение, и она выполнялась в течение 6-8 часов.
  • 0
    @csadam Что вы рекомендуете для больших строк, где конечной целью является удаление дубликатов, чтобы избежать ограничения 4000 байтов varchar2?
Показать ещё 1 комментарий
1

Мне всегда приходилось писать PL/SQL для этого или просто конкатенировать ',' в поле и копировать в редактор и удалять CR из списка, дающего мне одну строку.

То есть

select country_name||', ' country from countries

Немного длинный ветер в обоих направлениях.

Если вы посмотрите на Ask Tom, вы увидите множество возможных решений, но все они вернутся к типу объявлений и/или PL/SQL

Спросить Tom

Поделиться
0 
SELECT REPLACE(REPLACE
((SELECT     TOP (100) PERCENT country_name + ', ' AS CountryName
FROM         country_name
ORDER BY country_name FOR XML PATH('')), 
'&<CountryName>', ''), '&<CountryName>', '') AS CountryNames
Поделиться
-3

вы можете использовать этот запрос для выполнения указанной выше задачи.

DECLARE @test NVARCHAR(max)
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test SELECT field2= @test

для подробностей и пошагового объяснения посетите следующую ссылку
http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html

Поделиться

Ещё вопросы

Сообщество Overcoder
Наверх
Меню