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

Question

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

этот вопрос уже есть ответ здесь:

SQL-запрос для объединения значений столбцов из нескольких строк в Oracle

13 ответов

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

select * from countries

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

country_name

------------

Albania

Andorra

Antigua

.....

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

Albania, Andorra, Antigua, ...

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

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

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

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

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;

902 11

sql oracle string-aggregation concatenation pivot

11 ответов:

Comments

Ничего не найдено.

Daniel Emge · Accepted Answer · 2018-02-02 21:18:49

вот простой способ без 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.

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

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

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

1 row selected.

JoshL · Accepted Answer · 2014-11-11 14:39:52

The WM_CONCAT функция (если она включена в вашу базу данных, предварительно 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;
Подробнее/параметры

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

Makatun · Accepted Answer · 2016-12-30 12:28:40

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

Gaya3 · Accepted Answer · 2013-03-15 15:33:32

вы можете попробовать этот запрос.
select listagg(country_name,',') within group (order by country_name) cnt 
from countries; 

Decci.7 · Accepted Answer · 2013-08-18 07:09:05

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

SELECT RTRIM (
          XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
          ',')
          country_name
  FROM countries;

tuinstoel · Accepted Answer · 2009-01-22 15:10:05

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

вы также можете сделать это:
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
посетите сайт спросите Тома и выполните поиск по "stragg" или "String concatenation". Очень много образцы. Существует также не документированная функция oracle для достижения ваших потребностей.

tips · Accepted Answer · 2011-04-10 21:34:16

Мне нужна была подобная вещь, и я нашел следующее решение.
select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from  

David MacIntosh · Accepted Answer · 2012-11-17 00:27:47

в этом примере мы создаем функцию, чтобы привести запятую очерченный список различных причин удержания счета AP уровня строки в одно поле для запроса уровня заголовка:
 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; 

Andrew Wood · Accepted Answer · 2011-04-10 21:32:00

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

то есть
select country_name||', ' country from countries
немного долго наматывал в обе стороны.

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

Спросить Тома

user1626874 · Accepted Answer · 2012-08-27 16:02:35

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

Rashmi Kant · Accepted Answer · 2011-11-08 22:11:02

вы можете использовать этот запрос для выполнения вышеуказанной задачи
DECLARE @test NVARCHAR(max)
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test SELECT field2= @test
для подробного и пошагового объяснения посетите следующее link
http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html