Transforming from list to a set of rows
Posted by Riyaj Shamsudeen on January 11, 2007
A developer asked me how to transform a column with a list of values to a set of rows ?
Column value is of this format:
Requirement was to convert above value to the format below:
It is easy to do this using a pipelined function. Rows returned from a function can be used as a row source, as if it is a table row source. Metalink document 136909.1 describes this pipelined functions excellently.
Here is the test case, for the solution. Some part of code is borrowed from Tom Kyte’s original asktom posting.
– Dropping, recreate table tlist and populate with two rows.
drop table tlist;
create table tlist (c1 number, c2 varchar2(30) );
insert into tlist values (1,’100,101,102,103,104,105′);
insert into tlist values (2,’200,201,202,203,204,205′);
– Pipelined functions returns collections. Declaring a type TestListObj.
— TestList is an array of TestListObj
drop type TestList;
drop type TestListObj;
create or replace type TestListObj as object
(c1 number, c2 number);
create or replace type TestList as table of TestListObj;
— Creating a pipelined function.
REM This function :
REM for each row from tlist table
REM Read column values c1 and c2.
REM c2 has list of values separated by comma
REM For each value from the list
REM Construct TestListObj and store that as a member of TestList table object
REM Return TestList
create or replace function in_list_Test
TestList := TestList();
for l_csr in (select c1,c2 from tlist )
l_string := l_csr.c2 || ‘,';
exit when l_string is null;
n := instr( l_string, ‘,’ );
l_data(l_data.count) := TestListObj( l_csr.c1, ltrim( rtrim( substr( l_string, 1, n-1 ) ) ) );
l_string := substr( l_string, n+1 );
select * from table (cast (in_list_Test as TestList))
Hope, you can make use of this.