Segregating email ids of each customer (if more than one) by creating the new lines/records for that customer My customer Database contains more than one email id in “Email” Field due to which my emails getting bounced. I want to have only one email id in “Email” Field but at the same time, I don’t want to lose any other email id also. 

I want that if there are more than one email id in “Email” field then new lines (equivalent to total no. of email ids) to be created for that customer with one email id only in “Email” field and data in the rest of the fields should be copied as it is for that particular customer. 

Following is the script.

CREATE TABLE mytable (
    NAME VARCHAR(26) NOT NULL PRIMARY KEY
    ,City VARCHAR(10) NOT NULL
    ,EMail VARCHAR(79) NOT NULL
    ,Contact VARCHAR(33) NOT NULL
    );

INSERT INTO mytable 
(NAME, City, EMail, Contact)
VALUES 
('EFG', 'Chennai', 'eminent5sales@gmail.com;eminentyusuf@gmail.com', 'Mr. Y'),
('ABC', 'AHMEDABAD', 'priyalcorporation@gmail.com', 'Mr. N'),
('XYZ', 'Ahemadabad', 'Dhruv_Patel@milacron.co;mayur_a_patel@milacron.com;Bharat_R_Parekh@milacron.com', 'Mr. J');


Expected Output

+------+------------+------------------------------------------------+---------+
| Name |    City    |                     E-Mail                     | Contact |
+------+------------+------------------------------------------------+---------+
| EFG  | Chennai    | eminent5sales@gmail.com;eminentyusuf@gmail.com | Mr. Y   |
| EFG  | Chennai    | eminentyusuf@gmail.com                         | Mr. Y   |
| ABC  | AHMEDABAD  | priyalcorporation@gmail.com                    | Mr. N   |
| XYZ  | Ahemadabad | Dhruv_Patel@milacron.co                        | Mr. J   |
| XYZ  | Ahemadabad | mayur_a_patel@milacron.com                     | Mr. J   |
| XYZ  | Ahemadabad | Bharat_R_Parekh@milacron.com                   | Mr. J   |
CREATE TABLE mytable (
    NAME VARCHAR(26) NOT NULL PRIMARY KEY
    ,City VARCHAR(10) NOT NULL
    ,EMail VARCHAR(79) NOT NULL
    ,Contact VARCHAR(33) NOT NULL
    );

GO




INSERT INTO mytable 
(NAME, City, EMail, Contact)
VALUES 
('EFG', 'Chennai', 'eminent5sales@gmail.com;eminentyusuf@gmail.com', 'Mr. Y'),
('ABC', 'AHMEDABAD', 'priyalcorporation@gmail.com', 'Mr. N'),
('XYZ', 'Ahemadabad', 'Dhruv_Patel@milacron.co;mayur_a_patel@milacron.com;Bharat_R_Parekh@milacron.com', 'Mr. J');
GO




select * from mytable;
GO


NAME | City       | EMail                                                                           | Contact
:--- | :--------- | :------------------------------------------------------------------------------ | :------
ABC  | AHMEDABAD  | priyalcorporation@gmail.com                                                     | Mr. N  
EFG  | Chennai    | eminent5sales@gmail.com;eminentyusuf@gmail.com                                  | Mr. Y  
XYZ  | Ahemadabad | Dhruv_Patel@milacron.co;mayur_a_patel@milacron.com;Bharat_R_Parekh@milacron.com | Mr. J  




 SELECT A.[NAME],A.[City],
     Split.a.value('.', 'VARCHAR(100)') AS [Email],[Contact] 
 FROM  (SELECT [NAME],[City],  
         CAST ('&lt;M&gt;' + REPLACE([Email], ';', '&lt;/M&gt;&lt;M&gt;') + '&lt;/M&gt;' AS XML) AS [Email],[Contact] 
     FROM  mytable) AS A CROSS APPLY Email.nodes ('/M') AS Split(a); 
GO


NAME | City       | Email                        | Contact
:--- | :--------- | :--------------------------- | :------
ABC  | AHMEDABAD  | priyalcorporation@gmail.com  | Mr. N  
EFG  | Chennai    | eminent5sales@gmail.com      | Mr. Y  
EFG  | Chennai    | eminentyusuf@gmail.com       | Mr. Y  
XYZ  | Ahemadabad | Dhruv_Patel@milacron.co      | Mr. J  
XYZ  | Ahemadabad | mayur_a_patel@milacron.com   | Mr. J  
XYZ  | Ahemadabad | Bharat_R_Parekh@milacron.com | Mr. J  


dbfiddle here