Friday, November 03, 2006

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)

15 comments:

ESTEBAN ALVINO Q. said...

Hello man
After a hard searching i arrive to your post, its what i need, but its a pity that its in oracle, i need that logic for Sql Server 2005, you try that code in sql, or know some code that same the logic that you get.

Thanks in advance
Bye

Jason said...

Sorry. I haven't done any SQL Server in a long time. Solution 1 is very Oracle specific. However, a version of Solution 2 should be possible. DECODE is Oracle's old-style "if" and "DUAL" is a pseudotable.

Anonymous said...

Thank you for issue, it's helping me to create cross tab report!

THANK!!

Anonymous said...

I don't seem to get quite the desired result. Not changing anything in your function other then the table schema, I run this query:

select (split(R_R_KEY_NUMBER)) from gins_owner.crtv_asset_metadata
where submission_batch_id = '2565718';

The result is (DATASET) for each row returned. I am using Oracle 9.i in case that makes a difference.

Jason said...

Typo in script fixed!

Nadhilah said...
This comment has been removed by the author.
Anonymous said...

Hallo Jason,
thanks for Your hints, it's very clever solution.
It didn't help in my particular problem. I don't know how meny tokens are stored in single field so I googled further and found this on ORACLE WEB-Site.
This example suits me better:

Tip of the Week
Tip for Week of July 9, 2007

Split a String

This tip comes from Brian Eye, a DBA, in Bristow, VA.


The following splits up the values returned from a string.



SQL> create table tab1 (owner number, cars varchar2(200));

Table created.

SQL> insert into tab1 (
select 1, 'Ford,Toyota,Nissan' from dual union all
select 2, 'Lexus,Mercedes,BMW,Infiniti' from dual union all
select 3, 'Ferrari' from dual union all
select 4, 'Porsche,Lotus,Lamborghini,Maserati,Aston Martin' from dual union all
select 5, 'Maybach,Bentley' from dual);

5 rows created.

SQL> col cars format a50
SQL> set pages 100
SQL> select * from tab1;

OWNER CARS
---------- --------------------------------------------------
1 Ford,Toyota,Nissan
2 Lexus,Mercedes,BMW,Infiniti
3 Ferrari
4 Porsche,Lotus,Lamborghini,Maserati,Aston Martin
5 Maybach,Bentley

SQL> col car format a20
SQL> break on owner

-- Oracle Database 10g version (using regular expressions)

SQL> select owner, car
from (
select owner
, regexp_substr(str, '[^,]+', 1, level) car
, level lv
, lag(level, 1, 0) over (partition by owner order by level) lg
from (
select owner
, ','||cars str
from tab1)
connect by regexp_substr(str, '[^,]+', 1, level) is not null)
where lv != lg;

OWNER CAR
---------- --------------------
1 Ford
Toyota
Nissan
2 Lexus
Mercedes
BMW
Infiniti
3 Ferrari
4 Porsche
Lotus
Lamborghini
Maserati
Aston Martin
5 Maybach
Bentley

15 rows selected.

--Oracle9i & Oracle Database 10g version

SQL> select owner, car
from (
select owner
, trim(substr(str, instr(str, ',', 1, level) + 1,
instr(str, ',', 1, level + 1) - instr(str, ',', 1, level) - 1)) car
, level lv
, lag(level, 1, 0) over (partition by owner order by level) lg
from (
select owner, ','||cars||',' str
from tab1)
connect by instr(str, ',', 1, level) > 0)
where car is not null
and lv != lg;

OWNER CAR
---------- --------------------
1 Ford
Toyota
Nissan
2 Lexus
Mercedes
BMW
Infiniti
3 Ferrari
4 Porsche
Lotus
Lamborghini
Maserati
Aston Martin
5 Maybach
Bentley

15 rows selected.
Here the original link
http://www.oracle.com/technology/oramag/code/tips2007/070907.html

Regards
Rotax42

Unknown said...

Hello jason,
i have a table source:

DATA DN1 DN2 C1 C2
01/01/2009 15:00 1 1 37 34
01/01/2009 15:00 1 2 41 54
01/01/2009 15:00 2 1 57 25

i want a destination table:

DATA DN1 DN2 CXN CX
01/01/2009 15:00 1 1 C1 37
01/01/2009 15:00 1 1 C2 34
01/01/2009 15:00 1 2 C1 41
01/01/2009 15:00 1 2 C2 54
01/01/2009 15:00 2 1 C1 57
01/01/2009 15:00 2 1 C2 25

Thx Anto

Jason said...

I'm not sure what you are asking for...

Grouchy said...

You can also use:

SELECT LEVEL AS row_count
FROM DUAL
CONNECT BY ROWNUM BETWEEN 1 AND 10

to replace:

SELECT 1 AS row_count
FROM DUAL
UNION ALL
...
...
SELECT 10 AS row_count
FROM DUAL

which gets lengthy if you need more than a couple rows.

www.oracledba.in said...

i like this

Unknown said...

awsome piece of information, I had come to know about your website from my friend vinod, indore,i have read atleast seven posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, Single Row Function in sql

Arpit Jain said...

I have table product and i am displaying same products have different rates..but it will print on the same line ex..

PRODUCT_ID RATES
IND0001 $10$2$20$5

but i want..
Ind0001 $10
$20
$5
in dropdown menu..for fetching the row..

Jason Vogel said...

Solution 1 should work but pass $ as the split value

Unknown said...

Hi,
i have a requirement like
name
------
a,b,c

so i need a result like
name
-----
a
b
c

can u tell me how to do ....