SQL Server : split a column with varied data into 3 specific columns grouped by ID

user_4265326 0
Viewed 302

Using SQL Server, I'm trying to split information shared in one column into three based on the number of IDs. Ideally I'd have distinct IDs at the end.

There can be from 1-3 rows per PersonID depending on the information in the contact column.

If a personID appears more than once I'd like to have the data split into two columns, one for phone and one for email.

I'd need to check that the data contained an "@" symbol for it to be put into the Email column, and the rest put into Phone or Alt Phone.

It's pretty hard to explain so if you need any more information please comment.

Hopefully the example below will help:

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

I want to convert this into :

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
sql-server-2008 sql
3 Answers

Using row number and group by person id you can achieve the same by below query. 

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
user_55844130

You can do it using subqueries

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

Output

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
user_80674070 
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
user_85762730
Related