Home » SQL & PL/SQL » SQL & PL/SQL » multiple values in a field (9i)
multiple values in a field [message #315958] Wed, 23 April 2008 06:13 Go to next message
khresmoi
Messages: 24
Registered: October 2007
Junior Member
Hi,

I've a table like shown below with name and cars as the columns

Name		 Cars
 
Person1		 Car1,Car2,Car3
 
Person2		 Car1,Car4
 


I need to create a view as given below..

Name		 Cars
 
Person1		 Car1
 
Person1		 Car2
 
Person1		 Car3
 
Person2		 Car1
 
Person2		 Car4


How do I do this? Please give me some clues..

Thanks in advance.

Re: multiple values in a field [message #315962 is a reply to message #315958] Wed, 23 April 2008 06:16 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Clue is to search for keywords like stragg, unpivot, pivot, concat_all etc. Alternatively check this link.

http://www.oracle-developer.net/display.php?id=412

Regards

Raj

Re: multiple values in a field [message #315968 is a reply to message #315962] Wed, 23 April 2008 06:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think the best way to do this is to use a row generator to dupicate each row as many times as there are cars in it, and then simply prune the Cars data down to show only one value.

I appreciate that we shouldn't spoonfeed, but I lack the time and patience to coach someone through this query:
create table row_gen_test (person  varchar2(30), Cars varchar2(100));

insert into row_gen_test values ('Person1','Car1,Car2,Car3');
insert into row_gen_test values ('Person2','Car1,Car4');

select person
      ,cars
      ,instr(cars,',',1,(lvl))
      ,instr(cars,',',1,(lvl+1))
      ,substr(cars,instr(cars,',',1,lvl)+1,instr(cars,',',1,lvl+1)-instr(cars,',',1,lvl)-1) car,lvl
from (
select person,','||cars||',' cars,lvl
from   row_gen_test
      ,(select level lvl from dual connect by level < 100)
where  lvl <= (length(cars)-length(replace(cars,',','')))+1);
Re: multiple values in a field [message #315973 is a reply to message #315968] Wed, 23 April 2008 06:46 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I lack the time and patience to coach someone through this query

We could do it for you. Smile

Regards
Michel
Previous Topic: regarding fact tab
Next Topic: how to use wildcard character &
Goto Forum:
  


Current Time: Sat Dec 10 22:48:14 CST 2016

Total time taken to generate the page: 0.07159 seconds