Q

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.

This was first published in January 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close