I've a table with data as below:
Key CustNo 1 0431,0324,98731 2 12,32,45,56,67,878
How to write an SQL statement to get the result as:
1 0431 1 0324 1 98731 2 12 2 32 2 45
... and so on?
Short answer: don't do this in SQL.
Longer answer: you can do this with SQL, but the SQL is very complex and ugly. There is a semi-easy way to do it, provided there happens to exist a Customers table which has individual CustNo values as primary (or at least unique) key.
select m.Key , c.CustNo from myTable as m inner join Customers as c on ',' || m.CustNo || ',' like '%,' || c.CustNo || ',%'
While this works, it's not very efficient. You should consider normalizing your table.
Dig deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.