KS

• Mar 3rd, 2006

 

to create a result set where the rows need to be columns, or vice versa. You need to "pivot" rows into columns, or vice versa. this is where you need to look at a pivot (or crosstab) query.

Sourabh

• Mar 4th, 2006

 

create table src_table
(col1 varchar2(10)
,col2 varchar2(10)
,col3 varchar2(10)
,col4 varchar2(10)
,col5 varchar2(10)
);

insert into src_table values ('A', 'B', 'C', null, null);
insert into src_table values (null, 'B', 'C', null, null);
insert into src_table values (null, 'B', null, null, null);

-- unordered
select ilv1.col_val
from
(
select
decode(ilv_dual.rnum, 1, src_table.col1
                    , 2, src_table.col2
                    , 3, src_table.col3
                    , 4, src_table.col4
                    , 5, src_table.col5
      ) col_val
from
src_table,
(select level rnum
from   dual
connect by level <= 5
) ilv_dual
) ilv1
where ilv1.col_val is not null

-- ordered

select ilv1.col_val
from
(
select
src_table.rowid rid,
decode(ilv_dual.rnum, 1, src_table.col1
                    , 2, src_table.col2
                    , 3, src_table.col3
                    , 4, src_table.col4
                    , 5, src_table.col5
      ) col_val
from
src_table,
(select level rnum
from   dual
connect by level <= 5
) ilv_dual
) ilv1
where ilv1.col_val is not null
order by ilv1.rid
/