0

I have a table which contain two columns(ID, Email) and values as below.

ID    |email
 1    |abc@gmail.lk;efg@gmail.lk;rrr@hotmail.com
 2    |xyz@gmail.com;eee@gmail.lk

The problem is i need to retrieve those values as below by using sql server. 

ID  |email
1    abc@gmail.lk
1    efg@gmail.lk
1    rrr@hotmail.com
2    xyz@gmail.com
2    eee@gmail.lk
Haz
  • 321
  • 2
  • 8
  • 16
  • 1
    There are literally 100's of questions on SO on how to do this. If none of the answers in the above duplicate help, I suggest having a search on the site, as you'll find plenty more examples on how to do this. If you try, and fail, then instead post a new question showing your attempt(s), cite the answers you used, and explain why they didn't work. Thanks. – Thom A May 07 '19 at 10:38

1 Answers1

2

for sql server 2016 or > 2016

with cte as
(


 select 1 as id,   'abc@gmail.lk;efg@gmail.lk;rrr@hotmail.com' as email
 union all
 select 2    ,'xyz@gmail.com;eee@gmail.lk'
) select id,value from cte CROSS APPLY STRING_SPLIT(email, ';');

id  value
1   abc@gmail.lk
1   efg@gmail.lk
1   rrr@hotmail.com
2   xyz@gmail.com
2   eee@gmail.lk
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63