One Big concatenated string's data to be put in several rows 4

[uparna]

Hi ,
I have a table that has text like :

Code Order_Reasons
-----------------------------
PrimRose 1-2, 3-2, 12-5
Henna NULL
Basil 2-12,3-19,3-23,5-50,5-51

I have to put this data in another table as :

Code Order_Reasons
-----------------------------
PrimRose 1-2
PrimRose 3-2
PrimRose 12-5
Basil 2-12
Basil 3-19
Basil 3-23
Basil 5-50
Basil 5-51


This is actually a part of migration that i am doing and i want to know what the most efficient method to do it. I am going to do it in production only one time
(
we are absorbing an outdated application into a new enhanced one...the old one used to store all the order_reasons as one big concatenated string. Now i need to store it in separate rows ( for the reasons that are commaseparated) for the new app
)

Shall i just go ahead and write a small procedure for the same ,or is there a kill-bill query that can do the work for me..... (Eg : insert into new_table <killbill query> )?

Thanks!

Regards,
S. Jayaram Uparna .

 

[carp]

Sorry, but I think Bill lives through this one.
I would create a PL/SQL script to do this and be done with it.

Elbert, CO
1433 MDT

 

[SantaMufasa]

Uparna,

Agreeing with Carp, here is the code I propose:

declare
	hold_code	varchar2(50);
	hold_reasons	varchar2(1000);
begin
	for r in (select * from orders1) loop
		if r.order_reasons is not null then
			hold_code	:= r.code;
			hold_reasons	:= r.order_reasons;
			while length(hold_reasons)>0 loop
				if instr(hold_reasons,',') > 0 then
					insert into orders2 values
						(hold_code,trim(substr(hold_reasons,1,
							instr(hold_reasons,',')-1)));
					hold_reasons	:= substr(hold_reasons,
							instr(hold_reasons,',')+1);
				else
					insert into orders2 values
						(hold_code,trim(hold_reasons));
					hold_reasons	:= null;
				end if;
			end loop;
		end if;
	end loop;
end;
/
PL/SQL procedure successfully completed.

SQL> select * from orders2;

           Order
CODE       Reasons
---------- -------
PrimRose   1-2
PrimRose   3-2
PrimRose   12-5
Basil      2-12
Basil      3-19
Basil      3-23
Basil      5-50
Basil      5-51

8 rows selected.

Let me know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:52 (25May04) UTC (aka "GMT" and "Zulu"), 15:52 (25May04) Mountain Time)

 

[gunjan14]

Hi,

Thanks to Mufasa for providing the PL/SQL code. But if you still wants to go for plain SQL then here is the code

Step 1.
create a table with one column (number) and insert into it values till the data_length of Order_Reasons + 10
Say created a table temp_gunj with column seq_no (number) and inserted numbers 1 to 200

step 2.
Now give this query

insert into <new_table_name>
select 
    a.code,
    ltrim(rtrim(
        substr(','||a.order_reason||',',
               n.seq_no + 1, 
               p.seq_no - n.seq_no
              )))
from   temp_gunj n, 
       temp_gunj p, 
       <old_table_name> a
where  n.seq_no >= 1
and    n.seq_no < 
       length(','||a.order_reason||',')
and    substr(','||a.order_reason||',',
              n.seq_no,
              1
             ) = ','
and    p.seq_no = 
    (select min(b.seq_no)-1
     from   temp_gunj b
     where  b.seq_no between 1 and 
            length(','||a.order_reason||',')
     and    substr(','||a.order_reason||',', 
                   b.seq_no,
                   1
                  ) = ','
     and    b.seq_no > n.seq_no
    )
and    a.order_reason is not null;

Here is the example

create table coder
(
    code varchar2(10),
    order_reason varchar2(200)
);

create table new_coder
(
    code varchar2(10),
    order_reason varchar2(50)
);

create table temp_gunj
(
    seq_no number
);

insert into coder values ('PrimRose','1-2, 3-2, 12-5');
insert into coder values ('Henna',NULL);
insert into coder values ('Basil','2-12,3-19,3-23,5-50,5-51');

declare
begin
    for l_int in 1..[bold]210[/bold] loop
        insert into temp_gunj values (l_int);
    end loop;
end;
/

SQL> col order_reason format a50
SQL> set linesize 80
SQL> select * from coder;
CODE       ORDER_REASON
---------- -----------------------------------------------
PrimRose   1-2, 3-2, 12-5
Henna
Basil      2-12,3-19,3-23,5-50,5-51

Now to put data into new_coder table

insert into new_coder
select 
    a.code,
    ltrim(rtrim(
        substr(','||a.order_reason||',',
               n.seq_no + 1, 
               p.seq_no - n.seq_no
              )))
from   temp_gunj n, 
       temp_gunj p, 
       coder a
where  n.seq_no >= 1
and    n.seq_no < 
       length(','||a.order_reason||',')
and    substr(','||a.order_reason||',',
              n.seq_no,
              1
             ) = ','
and    p.seq_no = 
    (select min(b.seq_no)-1
     from   temp_gunj b
     where  b.seq_no between 1 and 
            length(','||a.order_reason||',')
     and    substr(','||a.order_reason||',', 
                   b.seq_no,
                   1
                  ) = ','
     and    b.seq_no > n.seq_no
    )
and    a.order_reason is not null;

Now

SQL> select * from new_coder;

CODE       ORDER_REASON
---------- -----------------------------------------------
PrimRose   1-2
PrimRose   3-2
PrimRose   12-5
Basil      2-12
Basil      3-19
Basil      3-23
Basil      5-50
Basil      5-51

Hope this helps

Regards,
Gunjan

 

[uparna]

what can i say.....i am speechless with delight. A big, fat, heartfelt thanks to both Santa (who has been my saviour many times) and Gunjan. Santa's approach was what i was expecting , but Gunjan's simple but exceptional idea is out-of-the-world. Thank you !

Regards,
S. Jayaram Uparna .

 

[lewisp]

Gunjans excellent solution got me thinking. This can be done without using a temporary table. Try this:

SELECT a.code,
       Substr(a.order_reasons,
              Decode(the_num,
                     0,1,
                     1,0,
                     Instr(a.order_reasons,',',1,b.the_num - 1)) + 1,
              Decode(Instr(a.order_reasons,',',1,b.the_num),
                     0,255,
                     Instr(a.order_reasons,',',1,b.the_num)) - 1
            - Decode(the_num,
                     0,1,
                     1,0,
                     Instr(a.order_reasons,',',1,b.the_num - 1)))
FROM  <your_table> a,
      (SELECT rownum the_num
       FROM all_objects) b
WHERE a.order_reasons IS NOT NULL
AND   Decode(the_num,
             1,1,
             Instr(a.order_reasons,',',1,b.the_num - 1)) != 0
order by 1,2

 

[SantaMufasa]

Uparna,

I would be interested in the performance differences between the above solutions. You probably have far more data than we have to create a reasonable test, out of curiosity, could you please post the results comparing your performance findings of the PL/SQL solution versus the clever SQL-only solution(s)?

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:24 (26May04) UTC (aka "GMT" and "Zulu"), 10:24 (26May04) Mountain Time)

 

[uparna]

Hi Santa ,
Here are the stats and they are pretty distinct:

the all-objects-single-query takes 20 Seconds,
the extra-table-single-query takes 04 seconds,
the procedure-approach tables......100.....milliseconds.

These are all based on second run , on my development database. Since i have to do this once on production, the time is not my first concern , but the results show that the procedure approach is infinitely faster than the query-approach. Something inside me always opts for complex queries rather than procedures , and i have to find it and kill it.

Thanks ! <such a small word>


Regards,
S. Jayaram Uparna .

 

[sem]

It might be even faster to create some package with declared collection type. Then populate some variable of that type from comma-delimited list and query from collection, not from some temporary table.

Regards, Dima

http://seminihin.narod.ru

 

[lewisp]

Out of interest, does the single query work any faster if you reverse the order of the tables in the FROM?

ie [tt]FROM (SELECT rownum the_num
FROM all_objects) b,
<your_table> a[/tt]

 

[complus]

is there any simple way to do the reverse, meaning multiple table rows to single row

This table
Code Order_Reasons
-----------------------------
PrimRose 1-2
PrimRose 3-2
PrimRose 12-5
Basil 2-12
Basil 3-19
Basil 3-23
Basil 5-50
Basil 5-51

to this table

Code Order_Reasons
-----------------------------
PrimRose 1-2, 3-2, 12-5
Basil 2-12,3-19,3-23,5-50,5-51

 

[SantaMufasa]

Complus,

What are your rules? Is PL/SQL in play?...Or SQL only?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:12 (22Jun04) UTC (aka "GMT" and "Zulu"), 09:12 (22Jun04) Mountain Time)

 

[complus]

preferably SQL

 

[SantaMufasa]

ComPlus,

How does a hybrid solution sound?

Section 1 -- Original table data

select * from orders3;

ORD_CODE        ORD_REASON
--------------- ----------
PrimRose        1-2
PrimRose        3-2
PrimRose        12-5
Basil           2-12
Basil           3-19
Basil           3-23
Basil           5-50
Basil           5-51

8 rows selected.

Section 2 -- The finished code and output:

col a heading "Order|Code" format a8
col b heading "Order|Reasons" format a40
Select distinct ord_code a, get_reasons(ord_code) b
from orders3
order by ord_code
/

Order    Order
Code     Reasons
-------- ----------------------------
Basil    2-12, 3-19, 3-23, 5-50, 5-51
PrimRose 1-2, 3-2, 12-5

2 rows selected.

Section 2 -- The PL/SQL function that gathers the reasons onto one line:

create or replace function get_reasons (ord_in varchar2) return varchar2 is
	hold_str	varchar2(200);
	prefix		varchar2(2);
begin
	for r in (select *
			from orders3
			where ord_code = ord_in
			order by to_number(substr
                              (ord_reason,1,instr(ord_reason,'-')-1))
			) loop
		hold_str	:= hold_str||prefix||r.ord_reason;
		prefix	:= ', ';
	end loop;
	return hold_str;
end;
/

Let us know if this is acceptable.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:52 (22Jun04) UTC (aka "GMT" and "Zulu"), 09:52 (22Jun04) Mountain Time)

 

[complus]

sounds good. thanks for the solution.

 

[uparna]

hi , i am kind of revisiting this and am trying if its possible using ONLY sql (without a func etc) in 9i --- does anyone have any idea?

Regards,
S. Jayaram Uparna .

 

[sem]

Do you need an ugly and not too efficient solution? I have one

SELECT code,
       TRIM(substr(order_reasons,
                   elm_start,
                   elm_end - elm_start + 1))
  FROM (SELECT code,
               order_reasons,
               decode(ind.elm,
                      1,
                      1,
                      instr(order_reasons, ',', 1, elm - 1) + 1) elm_start,
               decode(ind.elm,
                      num_entries,
                      length(order_reasons),
                      instr(order_reasons, ',', 1, elm) - 1) elm_end
          FROM (SELECT code,
                       length(t.order_reasons) -
                       length(REPLACE(t.order_reasons, ',')) + 1 num_entries,
                       order_reasons
                  FROM reasons t) dat,
               (SELECT rownum elm FROM user_tables) ind
         WHERE ind.elm <= num_entries)

It contains extra subselects for the sake of readability. But I strongly recommend you to redesigne your data structure to satisfy 1-st normal form.

Regards, Dima

http://seminihin.narod.ru

 

[uparna]

oops -- my bad -- i am revisiting the many-rows-to-a-single column approach --

ie

reasons
1
2
3

should come as

reasons
1,2,3



sorry for not clarifying -- my bad.

Regards,
S. Jayaram Uparna .