sql - Oracle - 从对偶中选择常量值作为 M x N 表

Question

目前我正在使用类似于以下语句的东西来创建类似结构的临时表。

WITH CONFIGURATION AS (
   SELECT 'some blah value' AS BLAH , 'some blee value' AS BLEE FROM DUAL
   UNION
   SELECT 'some other blah value' AS BLAH, 'some other blee value' AS BLEE FROM DUAL
)
SELECT 'BLAH BLAH' FROM CONFIGURATION C, SOME_OTHER_TABLE T WHERE C.BLAH=T.BLAH

我在这里面临的问题是，当我必须为 10x7 表结构实现相同的结构时，查询变得相当大和困惑。

有没有更简单的方法可以使用 DUAL 或任何其他系统表来实现这一点，而无需使用 UNION 并重写 SELECT FROM DUAL多次？

NOTE:

• I have no DDL permission on this database. Otherwise I would have created a table for achieving this already
• The database version is oracle 11g
• The data inside the M x N structure is to be will be irregular in nature

Answer 1

也许是这样的？

select * from (
  select trunc( (rownum - 1) / 3) as x, mod(rownum - 1, 3) as y, column_value
  from table( dbmsoutput_linesarray(
           'some blah value' , 'some blee value', 'some bluuu value',
           'some other blah value', 'some other blee value', 'some other bluuu value',
           'blah 5', 'blee 5', 'bluu 5',
           'blah 6', 'blee 6', 'bluu 6'
                ))
)
pivot (
  max( column_value )
  for y in ( 0 as blah, 1 as blee, 2 as bluuu )
)