String Split into column instead of rows

Question

Sample data:

id  email_address                                   email_new
-------------------------------------------------------------------------------
1   [email protected]                      [email protected]
2   [email protected]; [email protected]      [email protected]
2   [email protected]; [email protected]      [email protected]
3   [email protected]; [email protected]; [email protected]  [email protected]
3   [email protected]; [email protected]; [email protected]  [email protected]
3   [email protected]; [email protected]; [email protected]  [email protected]

I used the following query to split a '; ' separated string into rows:

SELECT
    id, email_address, email_new
FROM 
    (SELECT 
         id, email_address, 
         Split.a.value('.', 'NVARCHAR(max)') AS email_new 
     FROM
         (SELECT 
              id, email_address, CAST ('<M>' + REPLACE(email_address, '; ', '</M><M>') + '</M>' AS XML) email_new
          FROM 
              table) AS A 
     CROSS APPLY 
         email_new.nodes ('/M') AS Split(a)) x
GROUP BY
    id, email_address, email_new

Problem with my query is that I don't want to create a new row for every split email address- I would like to add a new column for it.. Ideally, something along the lines of this:

id  email_1                      email_2                email_3
------------------------------------------------------------------------------
1   [email protected]   null                   null
2   [email protected]         [email protected]   null
3   [email protected]                  [email protected]            [email protected]

There are as many as 3 separated email addresses in email_address column. Any suggestions? In case it grows beyond 3- it is best if answer could account for n columns.

Is there always a semicolon? you can just use substring(), also split it with the ID and you can just pivot it back into columns — Random_User
– Random_User, Commented May 14, 2018 at 19:55
@Random_User They are always separated the same way: semicolon and space '; ' — user8834780
– user8834780, Commented May 14, 2018 at 19:56
@M.Ali how many separated emails in email_address? 3 maximum — user8834780
– user8834780, Commented May 14, 2018 at 19:57
Thank you @JohnCappelletti - didn't see that previous post before posting. — user8834780
– user8834780, Commented May 14, 2018 at 20:28

M.Ali · Accepted Answer · 2018-05-14 20:04:24Z

Test Data

Declare @t table (Id INT, email_address VARCHAR(1000) , email_new VARCHAR(100));
INSERT INTO @t VALUES
(1   ,'[email protected]'                      , '[email protected]'),
(2   ,'[email protected]; [email protected]'      , '[email protected]'      ),
(2   ,'[email protected]; [email protected]'      , '[email protected]'      ),
(3   ,'[email protected]; [email protected]; [email protected]'  , '[email protected]'               ),
(3   ,'[email protected]; [email protected]; [email protected]'  , '[email protected]'               ),
(3   ,'[email protected]; [email protected]; [email protected]'  , '[email protected]'      );

Query

WITH Emails (ID , XmlEmail, email_new)
AS
(
    SELECT Id
          , CONVERT(XML,'<Emails><email>'  
                    + REPLACE(email_address,';', '</email><email>') 
                    + '</email></Emails>') AS XmlEmail
          , email_new
      FROM @t
)

 SELECT   ID
        , XmlEmail.value('/Emails[1]/email[1]','varchar(100)') AS Email1
        , XmlEmail.value('/Emails[1]/email[2]','varchar(100)') AS Email2
        , XmlEmail.value('/Emails[1]/email[3]','varchar(100)') AS Email3
        , XmlEmail.value('/Emails[1]/email[4]','varchar(100)') AS Email4
        , XmlEmail.value('/Emails[1]/email[5]','varchar(100)') AS Email5
        , email_new
FROM Emails

Result

╔════╦════════════════════════════╦═══════════════════════╦═══════════════════════╦═════════╦═════════╦════════════════════════════╗
║ ID ║           Email1           ║        Email2         ║        Email3         ║ Email4  ║ Email5  ║         email_new          ║
╠════╬════════════════════════════╬═══════════════════════╬═══════════════════════╬═════════╬═════════╬════════════════════════════╣
║  1 ║ [email protected] ║ NULL                  ║ NULL                  ║ NULL    ║ NULL    ║ [email protected] ║
║  2 ║ [email protected]       ║  [email protected] ║ NULL                  ║ NULL    ║ NULL    ║ [email protected]       ║
║  2 ║ [email protected]       ║  [email protected] ║ NULL                  ║ NULL    ║ NULL    ║ [email protected]       ║
║  3 ║ [email protected]                ║  [email protected]          ║  [email protected] ║ NULL    ║ NULL    ║ [email protected]                ║
║  3 ║ [email protected]                ║  [email protected]          ║  [email protected] ║ NULL    ║ NULL    ║ [email protected]                ║
║  3 ║ [email protected]                ║  [email protected]          ║  [email protected] ║ NULL    ║ NULL    ║ [email protected]       ║
╚════╩════════════════════════════╩═══════════════════════╩═══════════════════════╩═════════╩═════════╩════════════════════════════╝

thank you! I adjusted ';' to '; ' and it worked perfectly. Any chance for me not to specify # of columns- for it to be created based on # of '; ' split entries?
@user8834780 for that you will need to look into Dynamic Pivot. That is a whole different beast on it own.
Sounds scary, and like a brand new question. I'll leave it at that. Thank you!

Collectives™ on Stack Overflow

String Split into column instead of rows

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related