CODEWITHSUNDEEP
sqlsql-server-2008
TLCONE
TLCONE

Reputation: 104

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

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    [email protected]
2           Pudge   18191
2           Pudge   18182222
2           Pudge   [email protected]
3           Riki    [email protected]
3           Riki    19192
4           Lina    18424

I want to convert this into :

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

Upvotes: 2

Views: 409

Answers (3)

Rams
Rams

Reputation: 2159

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

Upvotes: 2

Bijan Ghasemi
Bijan Ghasemi

Reputation: 296

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

Upvotes: 2

Victor Hugo Terceros
Victor Hugo Terceros

Reputation: 3169

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','[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

Output

1   Chen    212747      NULL    [email protected]
2   Pudge   18182222    18191   [email protected]
3   Riki    19192       NULL    [email protected]
4   Lina    18424       NULL    NULL

Upvotes: 2

Related Questions