Link to home
Start Free TrialLog in
Avatar of pm-archtect
pm-archtectFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

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
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pm-archtect

ASKER

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?
Avatar of Sean Stuber
Sean Stuber

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.
This was a perfect answer - thank you and very prompt.