Jak połączyć kilka wierszy w listę rozdzielaną przecinkami w Oracle? [duplikat]

To pytanie ma już odpowiedź tutaj:

Mam proste zapytanie: 

select * from countries

Z następującymi wynikami: 

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

Chciałbym zwrócić wyniki w jednym wierszu, więc tak: 

Albania, Andorra, Antigua, ...

Oczywiście mogę napisać funkcję PL / SQL do zadanie (już zrobiłem w Oracle 10g), ale czy jest jakieś ładniejsze, najlepiej nie specyficzne dla Oracle rozwiązanie (lub może być wbudowana funkcja) do tego zadania?

Ogólnie używałbym go, aby uniknąć wielu wierszy w zapytaniu podrzędnym, więc jeśli dana osoba ma więcej niż jedno obywatelstwo, nie chcę, aby jej/jego był duplikatem na liście.

Moje pytanie opiera się na podobnym Pytaniu na SQL server 2005.

Aktualizacja : Moja funkcja wygląda tak: 

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;
Author: Community, 2009-01-22
Source

11 answers

Oto prosty sposób bez stragga lub tworzenia funkcji.

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.

Jak wspomnieli inni, jeśli używasz 11g R2 lub większego, możesz teraz użyć listagg, który jest znacznie prostszy.

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

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

1 row selected.
 62
Author: Daniel Emge,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-02-02 19:18:49

Funkcja WM_CONCAT (jeśli jest dołączona do twojej bazy danych, przed Oracle 11.2) lub LISTAGG (począwszy od Oracle 11.2) powinna dobrze zadziałać. Na przykład, otrzymuje się rozdzielaną przecinkami listę nazw tabel w schemacie: 

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

Lub 

select wm_concat(table_name) 
  from user_tables;

Więcej Szczegółów / opcje

Link do dokumentacji

 106
Author: JoshL,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-11-11 12:39:52

Dla Oracle możesz użyć LISTAGG

 18
Author: Makatun,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-30 10:28:40

Możesz spróbować tego zapytania.

select listagg(country_name,',') within group (order by country_name) cnt 
from countries;
 16
Author: Gaya3,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-03-15 13:33:32

Możesz również użyć tego: 

SELECT RTRIM (
          XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
          ',')
          country_name
  FROM countries;
 15
Author: Decci.7,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-08-18 04:09:05

Najszybszym sposobem jest użycie funkcji Oracle collect.

Możesz również to zrobić: 

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

Odwiedź Stronę zapytaj Toma i wyszukaj na "stragg" lub "string concatenation". Dużo przykłady. Istnieje również funkcja oracle, która nie jest udokumentowana, aby spełnić twoje potrzeby.

 4
Author: tuinstoel,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-01-22 13:10:05

Potrzebowałem podobnej rzeczy i znalazłem następujące rozwiązanie.

select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from
 2
Author: tips,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-04-10 18:34:16

W tym przykładzie tworzymy funkcję, która przenosi przecinkową listę różnych powodów przechowywania faktur AP na poziomie linii do jednego pola dla zapytania o poziom nagłówka: 

 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
Author: David MacIntosh,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-11-16 22:27:47

Zawsze musiałem napisać jakiś PL / SQL do tego lub po prostu konkatenować ',' do pola i skopiować do edytora i usunąć CR Z listy dając mi pojedynczą linię.

Czyli 

select country_name||', ' country from countries
/ Align = "left" /

Jeśli spojrzysz na Ask Tom zobaczysz mnóstwo możliwych rozwiązań, ale wszystkie powracają do deklaracji typu i/lub PL / SQL

Zapytaj Toma

 1
Author: Andrew Wood,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-04-10 18:32:00 
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
 0
Author: user1626874,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-08-27 13:02:35

Możesz użyć tego zapytania do wykonania powyższego zadania 

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

Aby uzyskać szczegóły i Wyjaśnienie krok po kroku, odwiedź następujące strony link
http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html

 -2
Author: Rashmi Kant,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-11-08 20:11:02