Como posso combinar várias linhas em uma lista delimitada por vírgula no Oracle?

Eu tenho uma consulta simples:

select * from countries 

com os seguintes resultados:

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

Gostaria de retornar os resultados em uma linha, assim, assim:

 Albania, Andorra, Antigua, ... 

É claro que posso escrever uma function PL / SQL para fazer o trabalho (eu já fiz no Oracle 10g), mas existe uma solução mais específica, preferencialmente não específica do Oracle (ou pode ser uma function interna) para esta tarefa. ?

Eu geralmente usaria isso para evitar várias linhas em uma subconsulta, portanto, se uma pessoa tiver mais de uma cidadania, não quero que ela seja uma duplicata na lista.

Minha pergunta é baseada na pergunta semelhante no SQL Server 2005 .

ATUALIZAÇÃO : Minha function é assim:

 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; 

    Aqui está uma maneira simples, sem stragg ou criar uma function.

     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. 

    Como outros já mencionaram, se você está no 11g R2 ou superior, agora você pode usar o listagg, que é muito mais simples.

     select listagg(country_name,', ') within group(order by country_name) csv from countries; CSV -------------------------- Albania, Andorra, Antigua 1 row selected. 

    A function WM_CONCAT (se incluída em seu database, pré Oracle 11.2) ou LISTAGG (iniciando o Oracle 11.2) deve funcionar bem. Por exemplo, isso obtém uma lista delimitada por vírgulas dos nomes das tabelas em seu esquema:

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

    ou

     select wm_concat(table_name) from user_tables; 

    Mais detalhes / opções

    Link para documentação

    Para o Oracle você pode usar o LISTAGG

    Você pode tentar esta consulta.

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

    Você pode usar isso também:

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

    A maneira mais rápida é usar a function de coleta do Oracle.

    Você também pode fazer isso:

     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 

    Visite o site, peça tom e pesquise ‘stragg’ ou ‘string concatenation’. Muitos exemplos. Há também uma function oracle não documentada para atender às suas necessidades.

    Eu precisava de uma coisa semelhante e encontrei a seguinte solução.

     select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from 

    Neste exemplo, estamos criando uma function para trazer uma lista delimitada por vírgulas de motivos distintos de retenção de fatura em AP em nível de linha em um campo para consulta em nível de header:

      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; 

    Eu sempre tive que escrever algum PL / SQL para isso ou apenas concatenar um ‘,’ para o campo e copiar em um editor e remover o CR da lista, dando-me a linha única.

    Isso é,

     select country_name||', ' country from countries 

    Um pouco longo fôlego nos dois sentidos.

    Se você olhar para o Ask Tom, verá várias soluções possíveis, mas todas elas reverterão para declarações de tipo e / ou PL / SQL

    Pergunte ao Tom

     SELECT REPLACE(REPLACE ((SELECT TOP (100) PERCENT country_name + ', ' AS CountryName FROM country_name ORDER BY country_name FOR XML PATH('')), '&', ''), '&', '') AS CountryNames 

    você pode usar essa consulta para fazer a tarefa acima

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

    para detalhes e explicação passo a passo, visite o seguinte link
    http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html