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

问题描述：

使用SQL Server，我试图根据ID的数量将一列中共享的信息拆分为三个。理想情况下，我最终会有不同的ID。SQL Server：将具有不同数据的列拆分为ID分组的3个特定列

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

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

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

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

希望下面的例子将帮助：

PersonID Name Contact 
---------------------------------------- 
1   Chen 212747 
1   Chen [email protected] 
2   Pudge 18191 
2   Pudge 18182222 
2   Pudge [email protected] 
3   Riki [email protected] 
3   Riki 19192 
4   Lina 18424

我想这个转换为：

PersonID Name Phone Alt Phone Email 
-------------------------------------------------------- 
1   Chen 212747 NULL  [email protected] 
2   Pudge 18191 18182222 [email protected] 
3   Riki 19192 NULL  [email protected] 
4   Lina 18424 NULL  NULL

答

declare @Table AS TABLE 
(
    PersonID INT , 
    Name VARCHAR(100), 
    Contact VARCHAR(100) 
) 
INSERT @Table 
     (PersonID, Name, Contact) 
VALUES 
(1   ,'Chen','212747'), 
(1   ,'Chen','[email protected]'), 
(2   ,'Pudge','18191'), 
(2   ,'Pudge','18182222'), 
(2   ,'Pudge','[email protected]'), 
(3   ,'Riki','[email protected]'), 
(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

非常感谢你，这似乎正是我所需要的 – TLCONE

@TLCONE p租赁，请阅读此规则https://stackoverflow.com/help/someone-answers，避免表示感谢，请将此答案标记为正确，并为帮助您的其他人添加+1。 –

答

被人ID使用行号和组可以实现通过下面的查询相同。

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

@VictorHugoTerceros其实我的答案是从我的手机在再寄一次再次编辑它这样做，它 – Rams

现在它是如何弹性的 –

答

可以使用子查询

declare @tbl table(PersonID int,Name varchar(50),Contact varchar(100)) 
insert into @tbl 
select 1,'Chen','212747' union 
select 1,'Chen','[email protected]' union 
select 2,'Pudge','18191' union 
select 2,'Pudge','18182222' union 
select 2,'Pudge','[email protected]' union 
select 3,'Riki','[email protected]' 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 [email protected] 
2 Pudge 18182222 18191 [email protected] 
3 Riki 19192  NULL [email protected] 
4 Lina 18424  NULL NULL

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

相关推荐