sql query on dual

Question

This:

    select * 
      from dual  
connect by level <= i  

...will return result as

1
2
3
...
i

Can the query be modified to get result row wise? i.e

1 2 3 .....i

Answer 1

The functionality you're after is called "pivot"--it's converting row data into columnar data. The opposite is called "unpivot". There is PIVOT/UNPIVOT syntax, but it isn't available until Oracle 11g.

On Oracle 9i+, CASE statements are used while prior to that you need to use DECODE to construct the logic so the values come out in columns. Here's an example for addressing if the limit to LEVEL is five on Oracle 9i+:

    SELECT MAX(CASE WHEN LEVEL = 1 THEN LEVEL END) AS one,
           MAX(CASE WHEN LEVEL = 2 THEN LEVEL END) AS two,
           MAX(CASE WHEN LEVEL = 3 THEN LEVEL END) AS three,
           MAX(CASE WHEN LEVEL = 4 THEN LEVEL END) AS four,
           MAX(CASE WHEN LEVEL = 5 THEN LEVEL END) AS five
      FROM DUAL
CONNECT BY LEVEL <= 5

The aggregate function MAX is necessary to "flatten" the resultset into a single row/record.

If you got this far, you're thinking "but I don't want to have to specify for every column--I want it to be dynamic based on i...". There's two issues:

1. There is a limit to the number of columns - see ORA-01792 maximum number of columns in a table or view is 1000. That goes for 8i & 9i, anyways. And frankly, needing to generate that many columns raises serious questions about what you're trying to do with the resultset, if there might be a better approach...
2. Dynamic requirements means using dynamic SQL

Answer 2

Try this:

select trim(both ',' from sys_connect_by_path(level, ',')) as string
  from dual
 where level = 100
connect by level <= 100

update: Testing the output:

SQL> 
SQL>  select trim(both ',' from sys_connect_by_path(level, ',')) as string
  2     from dual
  3    where level = 20
  4   connect by level <= 20
  5  /

STRING
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

another update: Guess I haven't read all comments well enough before posting, sorry. :) If you need to select each number as a separate column, then yes, see, OMG Ponies' answer - it's either pivot or dynamic SQL. :)