trasponer - sql oracle convertir columnas en filas

¿Cómo puedo combinar varias filas en una lista delimitada por comas en Oracle?

Question

Esta pregunta ya tiene una respuesta aquí:

• Consulta SQL para concatenar valores de columna de varias filas en Oracle 13 respuestas

Tengo una consulta simple:

select * from countries

con los siguientes resultados:

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

Me gustaría devolver los resultados en una fila, así que de esta manera:

Albania, Andorra, Antigua, ...

Por supuesto, puedo escribir una función PL / SQL para hacer el trabajo (ya lo hice en Oracle 10g), pero ¿existe una solución mejor, preferiblemente no específica de Oracle (o puede ser una función incorporada) para esta tarea? ?

En general, lo usaría para evitar filas múltiples en una sub consulta, por lo que si una persona tiene más de una ciudadanía, no quiero que sea un duplicado en la lista.

Mi pregunta se basa en la pregunta similar en SQL Server 2005 .

ACTUALIZACIÓN : Mi función se ve así:

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;

Answer 1

Aquí hay una manera simple sin Stragg o crear una función.

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 han mencionado otros, si tienes 11g R2 o más, ahora puedes usar listagg, que es mucho más simple.

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

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

1 row selected.

Answer 2

En este ejemplo, estamos creando una función para traer una lista delineada por comas de razones de retención de facturas AP de distintos niveles de línea en un campo para la consulta de nivel de encabezado:

 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; 

Answer 3

La forma más rápida es utilizar la función de recopilación de Oracle.

También puedes hacer esto:

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 el sitio ask tom y busque en ''stragg'' o ''string concatenation''. Muchos ejemplos También hay una función de oráculo no documentada para satisfacer sus necesidades.

Answer 4

La función WM_CONCAT (si está incluida en su base de datos, anterior a Oracle 11.2) o LISTAGG (iniciando Oracle 11.2) debería funcionar bien. Por ejemplo, esto obtiene una lista delimitada por comas de los nombres de tabla en su esquema:

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

o

select wm_concat(table_name) 
  from user_tables;

Más detalles / opciones

Enlace a la documentación

Answer 5

Necesitaba algo similar y encontré la siguiente solución.

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

Answer 6

Para Oracle puedes usar LISTAGG

Answer 7

Puedes usar esto también:

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

Answer 8

Siempre he tenido que escribir algo de PL / SQL para esto o simplemente concateno un '','' al campo y lo copio en un editor y elimino el CR de la lista que me da la línea única.

Es decir,

select country_name||'', '' country from countries

Un poco largo aliento en ambos sentidos.

Si miras Ask Tom verás muchas soluciones posibles, pero todas vuelven a las declaraciones de tipo y / o PL / SQL

Pregunta Tom

Answer 9

puedes probar esta consulta

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

Answer 10

puedes usar esta consulta para hacer la tarea anterior

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

para detalles y explicación paso a paso visita el siguiente enlace
http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html

Answer 11

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