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.
This was first published in January 2007