sql - transponer - ¿Existe una forma no fea de utilizar un literal de tabla de varias columnas y varias filas en una consulta de Oracle 11g?

oracle pivot subquery

Question

Estoy tratando de usar una tabla de varias columnas "literales" para una consulta de combinación con Oracle 11g.

Esto es lo mejor que he encontrado, basado en esta respuesta ( esta respuesta sugiere una sintaxis mucho más agradable, pero solo funciona para una tabla de columna única hasta donde yo sé):

SELECT * from
(
 -- The ugly... it burns...
 select ''APPLE'' as n, ''FRUIT'' as m from dual
 union all select ''CARROT'' as n, ''VEGGIE'' as m from dual
 union all select ''PEACH'' as n, ''FRUIT'' as m from dual
 union all select ''CUCUMBER'' as n, ''VEGGIE'' as m from dual
 union all select ''ORANGE'' as n, ''FRUIT'' as m from dual
)

¿Hay alguna manera menos fea de crear una tabla literal de múltiples filas y múltiples columnas en Oracle? Lamentablemente, no puedo crear tablas temporales.

<rant> Como he llegado a esperar, PostgreSQL tiene una sintaxis agradable y sensata para las tablas literales , pero Oracle es un desastre. </rant>

EDITAR: Probé la sintaxis del constructor de valores de fila como lo sugiere @Mark Chesney, pero eso tampoco funciona: select n,m from dual where (n,m) in (...) me da un error de Identificador no válido.

Answer 1

Para dos columnas, puede usar ODCIObjectList:

select objectschema m, objectname n
  from table(sys.ODCIObjectList(
    sys.odciobject(''APPLE'', ''FRUIT''), 
    sys.odciobject(''CARROT'', ''VEGGIE''),
    sys.odciobject(''PEACH'', ''FRUIT''),
    sys.odciobject(''CUCUMBER'', ''VEGGIE''),
    sys.odciobject(''ORANGE'', ''FRUIT'')));

M          N         
---------- ----------
APPLE      FRUIT     
CARROT     VEGGIE    
PEACH      FRUIT     
CUCUMBER   VEGGIE    
ORANGE     FRUIT 

Para más columnas, puede definir sus propios tipos:

create type t as object (a varchar2(10), b varchar2(10), c number);
create type tt as table of t;

select * from table( tt (
    t(''APPLE'', ''FRUIT'', 1),
    t(''APPLE'', ''FRUIT'', 1122), 
    t(''CARROT'', ''VEGGIE'', 3),
    t(''PEACH'', ''FRUIT'', 104),
    t(''CUCUMBER'', ''VEGGIE'', 5),
    t(''ORANGE'', ''FRUIT'', 6) ) )

A          B                   C
---------- ---------- ----------
APPLE      FRUIT               1 
APPLE      FRUIT            1122 
CARROT     VEGGIE              3 
PEACH      FRUIT             104 
CUCUMBER   VEGGIE              5 
ORANGE     FRUIT               6

Answer 2

Para muchas columnas puedes unirte así

SELECT a.v field1, b.v field2
  FROM (SELECT column_value v, rownum r
          FROM TABLE(sys.odcivarchar2list(''abc'', ''def'', ''ghi''))) a
  JOIN (SELECT column_value v, rownum r
          FROM TABLE(sys.odcivarchar2list(''abc'', ''def'', ''ghi''))) b ON a.r = b.r