Home » SQL & PL/SQL » SQL & PL/SQL » How do I convert an array in a string into rows?
How do I convert an array in a string into rows? [message #187066] Thu, 10 August 2006 14:13 Go to next message
Fred Easey
Messages: 73
Registered: January 2005
Member

Hi,

I have a table like this:

create table fe_test_1
   (
   foo char(1),
   bar varchar2(10)
   );
   
insert into fe_test_1 values ('a','1,2');
insert into fe_test_1 values ('a','1,2,3,4,5');
insert into fe_test_1 values ('a','8,9');
insert into fe_test_1 values ('a','8,9');
insert into fe_test_1 values ('a','10,12');

FOO BAR
--- ----------
a   1,2
a   1,2,3,4,5
a   8,9
a   8,9
a   10,12
a   1,2
a   1,2,3,4,5
a   8,9
a   8,9
a   10,12
a   1,2
a   1,2,3,4,5
a   8,9
a   8,9
a   10,12



and I need to turn it into this:

create table fe_test_2
   (
   foo char(1),
   bar varchar2(10),
   freq number(1)
   );
   
insert into fe_test_2 values ('a','1',3);
insert into fe_test_2 values ('a','2',2);
insert into fe_test_2 values ('a','3',1);
insert into fe_test_2 values ('a','4',1);
insert into fe_test_2 values ('a','5',1);
insert into fe_test_2 values ('a','8',2);
insert into fe_test_2 values ('a','9',2);
insert into fe_test_2 values ('a','10',1);
insert into fe_test_2 values ('a','12',1);

FOO BAR        FREQ
--- ---------- ----
a   1             3
a   2             2
a   3             1
a   4             1
a   5             1
a   8             2
a   9             2
a   10            1
a   12            1



So basically go through each record in fe_test_1, count the occurances of each "bar" for each "foo" and store the output in fe_test_2

I've been fiddling around with cursors and loops, but can't quite seem to crack it. Anyone got any ideas on how to do this?

Cheers,

Fred

Re: How do I convert an array in a string into rows? [message #187086 is a reply to message #187066] Thu, 10 August 2006 17:10 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I'm not understanding the logic here. You have 5 insert statements but 15 rows in the result. Are you running that script 3x?

Also, please explain the counts again. Why does the a/1 combo get a count of 3 and a/2 gets a count of 2?
Re: How do I convert an array in a string into rows? [message #187125 is a reply to message #187086] Fri, 11 August 2006 00:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If I understand correctly you want to pivot the comma-separated list into rows and count the frequency of each row. I'd say, have a look at this thread to get started.

MHE
Re: How do I convert an array in a string into rows? [message #187131 is a reply to message #187125] Fri, 11 August 2006 01:27 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
I would question why I was having to deal with comma-separated strings in the first place. It's expensive to parse them in PL/SQL. Do they come in from some application that doesn't have arrays?
Previous Topic: URGENT-counting the number of OS files of particuler type(extension) in a directory(windows)
Next Topic: anonymous block for procedure-ref cursor out variable
Goto Forum:
  


Current Time: Tue Dec 06 02:53:09 CST 2016

Total time taken to generate the page: 0.09085 seconds