Oracle 11g Split column value and then find matching rows to create single row

pm-archtect
pm-archtect used Ask the Experts™
on
Hi

I really don't know how to do this:

I want to split a value such as 123-456-789-0 into it's constituent parts and then find all the matching rows in a table and subsequently create a single row from the results.  If you review the attached file, you will see example data for the starting point and the end result.

It is important to understand that when the values are found and joined together, the resulting column order is important (as the example data will show you)

Many thanks
Split-Example.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
try this...
ee.txt

Author

Commented:
This is perfect - exactly what I needed.  May I be cheeky and extend this (I don't mind asking another question separtely if you want to grab that one).

I need to join the resulting list back to a table where the UNITID would be the key.  The only way to do this would be to see if any of the values in the CONSISTID match.  So I would want to split the CONSISTID again into consituent parts e.g. 10-202-312-30 becomes

10
202
312
30

and ask, do any of these values match the UNITID record in the other table, if so, then join the records together.  There will multiple rows in the other table with the same UNITID

As I say, perhaps I should ask this as a separate question?
Most Valuable Expert 2011
Top Expert 2012

Commented:
glad I could help,  yes,  adding new requirements should be a new question.

I'm not sure what you're asking in the addendum anyway,  be sure to include a complete example in the new question.

Author

Commented:
This was a perfect answer - thank you and very prompt.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial