Q
Problem solve Get help with specific problems with your technologies, process and projects.

Normalizing a comma-delimited list

I have a table with data as below. How to write an SQL statement to get this result?

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

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close