字符串拆分为列而不是行

一尘不染

字符串拆分为列而不是行

sql

样本数据：

id  email_address                                   email_new
-------------------------------------------------------------------------------
1   a.b@wellsfargoadvisors.com                      a.b@wellsfargoadvisors.com
2   c.b@RaymondJames.com; abc@RaymondJames.com      c.b@RaymondJames.com
2   c.b@RaymondJames.com; abc@RaymondJames.com      abc@RaymondJames.com
3   a.b@boa.com; bbc@boa.com; hh@bankofamerica.com  a.b@boa.com
3   a.b@boa.com; bbc@boa.com; hh@bankofamerica.com  bbc@boa.com
3   a.b@boa.com; bbc@boa.com; hh@bankofamerica.com  hh@bankofamerica.com

我使用以下查询将’ ;‘’分隔的字符串分成几行：

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

我的查询存在问题，我不想为每个拆分的电子邮件地址创建新行-我想为其添加新列。

id  email_1                      email_2                email_3
------------------------------------------------------------------------------
1   a.b@wellsfargoadvisors.com   null                   null
2   c.b@RaymondJames.com         abc@RaymondJames.com   null
3   a.b@boa.com                  bbc@boa.com            h@bankofamerica.com

email_address列中最多有3个单独的电子邮件地址。有什么建议？如果它的大小超过3，最好是答案可以占n列。

阅读 162

2021-03-08

共1个答案

一尘不染

测试数据

Declare @t table (Id INT, email_address VARCHAR(1000) , email_new VARCHAR(100));
INSERT INTO @t VALUES
(1   ,'a.b@wellsfargoadvisors.com'                      , 'a.b@wellsfargoadvisors.com'),
(2   ,'c.b@RaymondJames.com; abc@RaymondJames.com'      , 'c.b@RaymondJames.com'      ),
(2   ,'c.b@RaymondJames.com; abc@RaymondJames.com'      , 'abc@RaymondJames.com'      ),
(3   ,'a.b@boa.com; bbc@boa.com; hh@bankofamerica.com'  , 'a.b@boa.com'               ),
(3   ,'a.b@boa.com; bbc@boa.com; hh@bankofamerica.com'  , 'bbc@boa.com'               ),
(3   ,'a.b@boa.com; bbc@boa.com; hh@bankofamerica.com'  , 'hh@bankofamerica.com'      );

询问

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

结果

鈺斺晲鈺愨晲鈺愨暒鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺︹晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺︹晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺︹晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺︹晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺︹晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晲鈺愨晽
鈺� ID 鈺�           Email1           鈺�        Email2         鈺�        Email3         鈺� Email4  鈺� Email5  鈺�         email_new          鈺�

2021-03-08