Server :将具有不同数据的列拆分为3个按ID分组的特定列

Question

使用Server，我试图根据ID的数量将一列中共享的信息分成三列。理想情况下，我应该在最后有不同的ID。

根据联系人列中的信息，每个PersonID可以有1-3行。

如果一个personID出现不止一次，我希望将数据分成两列，一列用于电话，另一列用于电子邮件。

我需要检查数据是否包含一个"@“符号，以便将其放入Email列，其余的则放入Phone或Alt Phone。

这很难解释，所以如果你需要更多的信息，请评论。

希望下面的例子能有所帮助：

PersonID    Name    Contact
----------------------------------------
1           Chen    212747
1           Chen    Chen@test.com
2           Pudge   18191
2           Pudge   18182222
2           Pudge   Pudge@test.com
3           Riki    Riki@test.com
3           Riki    19192
4           Lina    18424

我想把它转换成：

PersonID    Name    Phone   Alt Phone   Email
--------------------------------------------------------
1           Chen    212747  NULL        Chen@test.com
2           Pudge   18191   18182222    Pudge@test.com
3           Riki    19192   NULL        Riki@test.com
4           Lina    18424   NULL        NULL

Answer 1

declare @Table AS TABLE
(
    PersonID    INT ,
    Name VARCHAR(100),
    Contact VARCHAR(100)
)
INSERT @Table
        ( PersonID, Name, Contact)
VALUES 
(1          ,'Chen','212747'),
(1          ,'Chen','Chen@test.com'),
(2          ,'Pudge','18191'),
(2          ,'Pudge','18182222'),
(2          ,'Pudge','Pudge@test.com'),
(3          ,'Riki','Riki@test.com'),
(3          ,'Riki','19192'),
(4          ,'Lina','18424')

SELECT 
    xQ.PersonID,
    xQ.Name,
    MAX(CASE WHEN xQ.IsEmail = 0 AND xQ.RowNumberPhone = 1 THEN xQ.Contact ELSE NULL END) AS Phone,
    MAX(CASE WHEN xQ.IsEmail = 0 AND xQ.RowNumberPhone = 2 THEN xQ.Contact ELSE NULL END) AS [Alt Phone],
    MAX(CASE WHEN xQ.IsEmail = 1 AND xQ.RowNumberEmail = 1 THEN xQ.Contact ELSE NULL END) AS Email
FROM
(
    SELECT *
        ,CASE WHEN PATINDEX('%@%',T.Contact)>0 THEN 1 ELSE 0 END AS IsEmail
        ,RANK() OVER(PARTITION BY T.PersonID, CASE WHEN PATINDEX('%@%',T.Contact)=0 THEN 1 ELSE 0 END ORDER BY T.Contact) AS RowNumberPhone
        ,RANK() OVER(PARTITION BY T.PersonID, CASE WHEN PATINDEX('%@%',T.Contact)>0 THEN 1 ELSE 0 END ORDER BY T.Contact) AS RowNumberEmail
    FROM @Table AS T
)AS xQ
GROUP BY 
    xQ.PersonID,
    xQ.Name
ORDER BY xQ.PersonID

Answer 2

使用行号和按人分组标识，您可以通过以下查询实现相同的结果。

Select PersonID, max(Name) name,
        max(case when rn=1 and contact not like '%@%' then contact end)  phone,
        max(case when rn=2 and contact not like '%@%' then contact end) Alt_Phone, 
        max(case when contact like '%@%' then contact end) mailid 
    from(select  t.*, row_number()  over(partition by personid order by contact) as rn from table t) as t2 
    group by PersonID

Answer 3

您可以使用子查询来完成它。

declare @tbl table(PersonID int,Name varchar(50),Contact varchar(100))
insert into @tbl
select  1,'Chen','212747' union
select  1,'Chen','Chen@test.com' union
select  2,'Pudge','18191' union
select  2,'Pudge','18182222' union
select  2,'Pudge','Pudge@test.com' union
select  3,'Riki','Riki@test.com' union
select  3,'Riki','19192' union
select  4,'Lina','18424'

SELECT DISTINCT 
 M.PersonID
,M.Name 
,(SELECT TOP 1 Contact FROM @tbl WHERE PersonID = M.PersonID AND Contact NOT LIKE '%@%' ORDER BY Contact) AS Phone
,(SELECT TOP 1 Contact FROM @tbl WHERE PersonID = M.PersonID AND Contact NOT LIKE '%@%' 
  AND Contact NOT IN (SELECT TOP 1 Contact FROM @tbl WHERE PersonID = M.PersonID AND Contact NOT LIKE '%@%' ORDER BY Contact)) AS AltPhone
,(SELECT TOP 1 Contact FROM @tbl WHERE PersonID = M.PersonID AND Contact LIKE '%@%') AS Email
FROM @tbl M

输出

1   Chen    212747      NULL    Chen@test.com
2   Pudge   18182222    18191   Pudge@test.com
3   Riki    19192       NULL    Riki@test.com
4   Lina    18424       NULL    NULL