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?

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top