separating records based on a column

Question

I have four columns .Two columns are comma separated. I am trying to get individual records for those comma separated values.

col  col2   col3   col4    
------------------------
1     1,2    2,3    4  
2     3,4    5      7  
4     5      3      5

My result set swill be

col1   col2   col3   col4  
--------------------------
1        1      2     4  
1        1      3     4  
1        2      2     4  
1        2      3     4  
2        3      5     7  
2        4      5     7  
4        5      3     5

I have tried many. But couldn't get exact dataset. Thanks in advance

Not so easy as I understand : https://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two — Grechka Vassili, May 23 '17 at 13:46

score 0 · Answer 1 · answered May 23 '17 at 14:02

Here is one method that uses two derived tables of numbers to get the nth element from the list:

select col1,
       substring_index(substring_index(col2, ',', n2.n), ',', -1) as col2,
       substring_index(substring_index(col3, ',', n3.n), ',', -1) as col3,
       col4
from t join
     (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
     ) n2
     on t.col2 like concat(repeat('%,', n2.n - 1), '%') join
     (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
     ) n3
     on t.col3 like concat(repeat('%,', n3.n - 1), '%')

separating records based on a column

1 Answers1