Oracle: SQL - Converting one row into two rows [multiple rows]

Problem : A single row needs to be converted into two rows.

Solution 1 :

Prerequite :

CREATE OR REPLACE TYPE PROD_TYPES.TYPE_STRING_ARRAY AS TABLE OF VARCHAR2(2000)

Function : This routine handles an unlimited number of desired rows as opposed to Solution 2.

CREATE OR REPLACE FUNCTION split (
 s_delimited_list_in     VARCHAR2,
 s_delimiter_in          VARCHAR2 := ',')
RETURN prod_types.type_string_array PIPELINED
IS
/*
@Usage Example:
select * from table(split('one,two,three'));
*/
 l_idx               PLS_INTEGER;
 l_list              VARCHAR2(32767) := s_delimited_list_in;
 l_value             VARCHAR2(32767);
 ls_delimiter        VARCHAR2(100) := NVL(s_delimiter_in,',');
BEGIN
LOOP
 l_idx := INSTR(l_list,ls_delimiter);

 IF (l_idx > 0) THEN

    PIPE ROW(SUBSTR(l_list,1,l_idx-1));
    l_list := SUBSTR(l_list,l_idx+LENGTH(ls_delimiter));
  
 ELSE

    PIPE ROW(l_list);
    EXIT;
  
 END IF;
END LOOP;

RETURN;

END SPLIT;

Sorry, there was a typo in the above script. It was fixed on 2/9/2009. Jason

The reverse of "split" is "joinrows".

Solution 2 :

Here is a simple example of converting one row into multiple rows. "atomic_contract" is returning a single row, but we need two columns from it to be returned as two rows.

select *
from (
select ac.*,
decode(t.row_count,1,ac.sk_seller,
                  2,ac.sk_buyer, null) sk_customer
from atomic_contract ac,
( select 1 row_count from dual
union all
select 2 row_count from dual) t
where contract_id = 3562777)