today one of my collague asked me to how to fetch all rows of a parameter table in a single row with oracle sql.

so i wrote this.

select * from
(select * from (select rownum as rnum, c.* from ( select b.* from user.test b order by rowid) c) where rnum = 1),
(select * from (select rownum as rnum, c.* from ( select b.* from user.test b order by rowid) c) where rnum = 2),
(select * from (select rownum as rnum, c.* from ( select b.* from user.test b order by rowid) c) where rnum = 3),
(select * from (select rownum as rnum, c.* from ( select b.* from user.test b order by rowid) c) where rnum = 4),
(select * from (select rownum as rnum, c.* from ( select b.* from user.test b order by rowid) c) where rnum = 5),
(select * from (select rownum as rnum, c.* from ( select b.* from user.test b order by rowid) c) where rnum = 6),
(select * from (select rownum as rnum, c.* from ( select b.* from user.test b order by rowid) c) where rnum = 7),
(select * from (select rownum as rnum, c.* from ( select b.* from user.test b order by rowid) c) where rnum = 8),
(select * from (select rownum as rnum, c.* from ( select b.* from user.test b order by rowid) c) where rnum = 9),
(select * from (select rownum as rnum, c.* from ( select b.* from user.test b order by rowid) c) where rnum = 10)

example gets 1o rows in a single row, actually it was constant 78 rows.
so he could used it like
resultset.getString(“param1″+i) with a loop.

and then he also needed 5 by 5 collected results with 2x columns in each view.

and my solution is

select * from
(select rownum as rx, b.* from (
select rownum as rnum, rowid as rid, a.* from (select param_a, param_b, param_c, param_d, param_e from user.test order by param_a, param_b, param_c) a) b
where rnum >=5 and rnum < 10) x,
(select rownum as rx, b.* from (
select rownum as rnum, rowid as rid, a.* from (select param_a, param_b, param_c, param_d, param_e from user.test order by param_a, param_b, param_c) a) b
where rnum >=10 and rnum < 15) y where x.rnum + 5 = y.rnum ;

anyone knows easier and more optimised way to do these?

Tags: fetch rows, get all rows in a single row, oracle, oracle sql, sql