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