Advertisement

07.10.2008 at 08:08AM PDT, ID: 23554053 | Points: 250
[x]
Attachment Details

How to split a column(row) having a comma delimited string as data into multiple rows

Asked by sriramsenior in Oracle Database, Oracle 10.x, PL / SQL

Tags: Oracle, Oracle Database, 10g, SQL

Hi
I have a table with comma separated strings as data in one of  its 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.

thanks,
SriramStart Free Trial
[+][-]07.10.2008 at 08:20AM PDT, ID: 21974269

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.10.2008 at 09:24AM PDT, ID: 21975013

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.10.2008 at 09:38AM PDT, ID: 21975167

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628