Home » SQL & PL/SQL » SQL & PL/SQL » How to split a row having a delimited string as one of the columns into multiple rows (Oracle 10g)  () 1 Vote
How to split a row having a delimited string as one of the columns into multiple rows [message #333126] Thu, 10 July 2008 09:55 Go to next message
sriramsenior
Messages: 1
Registered: July 2008
Junior Member
I have a table with comma separated strings as data in one of it’s columns. The string is always comma separated but it can have any number of tokens. For example:

Table: MyOrder

OrderNo SystemName
1000 UPLEX,DPLEX,MPLEX,APLEX
1001 BPLEX
1002 APLEX,DPLEX


I want to split this string and then generate a view which holds each token separately in a different row. i.e I want to have only one system per row.

View : V_MyOrder


OrderNo SystemName
1000 UPLEX
1000 DPLEX
1000 MPLEX
1000 APLEX
1001 BPLEX
1002 APLEX
1002 DPLEX

I would like to know what are all the systems associated with an order, one at a time, so that I can do a join with some other systems table that I have in my database to get more information. Can we get this into a view ?

No procedures/functions please.

regards
Sriram

Re: How to split a row having a delimited string as one of the columns into multiple rows [message #333128 is a reply to message #333126] Thu, 10 July 2008 10:02 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>No procedures/functions please.
Then no answer & You're On Your Own (YOYO)!
Re: How to split a row having a delimited string as one of the columns into multiple rows [message #333148 is a reply to message #333126] Thu, 10 July 2008 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you search for "pivot" or "unpivot" or "column to rows" you will find answers.

Regards
Michel
Re: How to split a row having a delimited string as one of the columns into multiple rows [message #333323 is a reply to message #333148] Fri, 11 July 2008 04:24 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you could use a row-generator, and use Substr/Instr (or possibly a Regexp) to get the string one bit at a time.
Previous Topic: Table Determination
Next Topic: sequence with "NO ORDER"
Goto Forum:
  


Current Time: Sun Dec 04 10:46:11 CST 2016

Total time taken to generate the page: 0.10095 seconds