Combaine rows into one row with partial information match

Question

Combaine rows into one row with partial information match

Debilon 431

I have a table with phone numbers and emails
few of the rows partially match : Same first & Last Name , Same Address different phone numbers and sometimes different email

 -- DDL and sample data population, start  

 DECLARE @PhoneTable Table (ID INT IDENTITY PRIMARY KEY, Email VARCHAR(30),FirstName VARCHAR(30), LastName Varchar(30), Address varchar(50), City Varchar(30), State Varchar(10), Zip Varchar(10),  
 PhoneNumber VARCHAR(30));  



 INSERT INTO @PhoneTable (Email, FirstName, LastName,Address,City,State,Zip,PhoneNumber) VALUES  
 (Null,' Darla', 'Shmooz', '81 Grass Valley DR', 'New York', 'NY', '10021', '829-514-7725'),  
 ('D.Shmooz'@gmail.com',' Darla', 'Shmooz', '81 Grass Valley DR', 'New York', 'NY', '10021', '829-514-7725'),  
 (Null,' Darla', 'Shmooz', '81 Grass Valley DR', 'New York', 'NY', '10021', '829-914-8825')  


  ;WITH MyCTE AS (  
   Select Distinct  
  Email, FirstName, LastName,Address,City,State,Zip,PhoneNumber  
   FROM @PhoneTable   
   )  
   Select * from MyCTE

Current Result

I need to combine the rows into a one single row with all available information from the table

I don't know how many emails / phones exist in the table.

Desired Output
<code>196467-image.png</code>

I'm Using Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64) May 27 2021 17:34:14 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

Olaf Helper 47,441 Reputation points

2022-04-26T10:43:53.1+00:00
I don't know how many emails / phones exist in the table.

And which result do you expect, when there are 5 or 10 or 20 phone numbers?
Debilon 431 Reputation points

2022-04-26T14:56:03.327+00:00

all of them in a one continuous line
Naomi Nosonovsky 8,431 Reputation points

2022-04-26T15:37:21.137+00:00

Do you want to generate your column names dynamically or you want to aggregate them as a string ?
Debilon 431 Reputation points

2022-04-26T17:41:39.107+00:00

Hard question as I just discovered someone with over 30 different emails in my data

I think that I would like to dynamically generate columns for up to 3 different emails and phones, anything over that should be aggregated to the last column of email/phone string.
Naomi Nosonovsky 8,431 Reputation points

2022-04-26T17:52:53.133+00:00

In other words, PhoneNumber3 will have all phones starting from 3rd separated by comma and same for emails.

This is slightly tricky, can you post a better sample. I can see the idea using ROW_NUMBER() function (two of them - one for emails, one for phones) and using string_agg function for concat of phones and emails and pre-defined structure of the table.

Accepted answer

1 additional answer

Your answer

Olaf Helper 47,441 Reputation points

2022-04-26T10:43:53.1+00:00

I don't know how many emails / phones exist in the table.

And which result do you expect, when there are 5 or 10 or 20 phone numbers?
Debilon 431 Reputation points

2022-04-26T14:56:03.327+00:00

all of them in a one continuous line
Naomi Nosonovsky 8,431 Reputation points

2022-04-26T15:37:21.137+00:00

Do you want to generate your column names dynamically or you want to aggregate them as a string ?
Debilon 431 Reputation points

2022-04-26T17:41:39.107+00:00

Hard question as I just discovered someone with over 30 different emails in my data

I think that I would like to dynamically generate columns for up to 3 different emails and phones, anything over that should be aggregated to the last column of email/phone string.
Naomi Nosonovsky 8,431 Reputation points

2022-04-26T17:52:53.133+00:00

In other words, PhoneNumber3 will have all phones starting from 3rd separated by comma and same for emails.

This is slightly tricky, can you post a better sample. I can see the idea using ROW_NUMBER() function (two of them - one for emails, one for phones) and using string_agg function for concat of phones and emails and pre-defined structure of the table.

Answer 1

LiHong-MSFT 10,056

Hi @Debilon
For STRING_AGG function, you can use Stuff instead.
And for ‘No Group By in an update’ issue，you may try put the 'Group By' query inside a subquery or another CTE.
Check this :

CREATE Table #PhoneTable  
(ID INT IDENTITY PRIMARY KEY, Email VARCHAR(30),FirstName VARCHAR(30),LastName Varchar(30),   
 Address varchar(50), City Varchar(30), State Varchar(10), Zip Varchar(10), PhoneNumber VARCHAR(30));  
                  
INSERT INTO #PhoneTable (Email, FirstName, LastName,Address,City,State,Zip,PhoneNumber) VALUES  
  (Null,' Darla', 'Shmooz', '81 Grass Valley DR', 'New York', 'NY', '10021', '829-514-7725'),  
  ('******@gmail.com',' Darla', 'Shmooz', '81 Grass Valley DR', 'New York', 'NY', '10021', '829-514-7725'),  
  (Null,' Darla', 'Shmooz', '81 Grass Valley DR', 'New York', 'NY', '10021', '829-914-8825'),  
  ('******@gmail.com',' Darla', 'Shmooz', '81 Grass Valley DR', 'New York', 'NY', '10021', '829-914-9925'),  
  (Null,' Darla', 'Shmooz', '81 Grass Valley DR', 'New York', 'NY', '10021', '829-614-6625')  
  
SELECT * FROM #PhoneTable  
  
;WITH CTE AS  
(  
 SELECT *,DENSE_RANK()OVER(PARTITION BY FirstName, LastName,Address,City,State,Zip ORDER BY ISNULL(email,'')DESC) AS RNum_Email  
         ,DENSE_RANK()OVER(PARTITION BY FirstName, LastName,Address,City,State,Zip ORDER BY ISNULL(PhoneNumber,'')DESC) AS RNum_PhoneNumber  
 FROM #PhoneTable  
),CTE2 AS   
(   
 SELECT FirstName,LastName,Address,City,State,Zip  
       ,MAX(CASE WHEN RNum_Email=1 THEN Email END) AS Email_1  
	   ,MAX(CASE WHEN RNum_Email=2 THEN Email END) AS Email_2  
       ,STUFF((SELECT DISTINCT',' + Email FROM CTE WHERE RNum_Email > 2 FOR XML PATH('')),1,1,'') AS Email_3  
       ,MAX(CASE WHEN RNum_PhoneNumber=1 THEN PhoneNumber END) AS PhoneNumber_1  
	   ,MAX(CASE WHEN RNum_PhoneNumber=2 THEN PhoneNumber END) AS PhoneNumber_2  
       ,STUFF((SELECT DISTINCT',' + PhoneNumber FROM CTE WHERE RNum_PhoneNumber > 2 FOR XML PATH('')),1,1,'') AS PhoneNumber_3  
 FROM CTE  
 GROUP BY FirstName,LastName,Address,City,State,Zip  
)  
SELECT * FROM CTE2 --Check IF Correct Before Update  
--UPDATE CTE2 SET Column...=Column...

Best regards,
LiHong

Debilon 431 Reputation points

2022-04-27T07:26:34.337+00:00

If you'll add another line to your insert statement

('******@gmail.com',' Ronen', 'Grooz', '408 East 68 Street', 'New York', 'NY', '10021', '710-805-0036')

You'll see that PhoneNUmber_3 associated with Darla', 'Shmooz is added to the Ronen Grooz row though it is not associated to Ronen Grooz.

LiHong-MSFT 10,056

Hi @Debilon
How about this:

;WITH CTE AS  
(  
 SELECT *,DENSE_RANK()OVER(PARTITION BY FirstName, LastName,Address,City,State,Zip ORDER BY ISNULL(email,'')DESC) AS RNum_Email  
         ,DENSE_RANK()OVER(PARTITION BY FirstName, LastName,Address,City,State,Zip ORDER BY ISNULL(PhoneNumber,'')DESC) AS RNum_PhoneNumber  
 FROM #PhoneTable  
),CTE2 AS   
(   
 SELECT FirstName,LastName,Address,City,State,Zip  
       ,MAX(CASE WHEN RNum_Email=1 THEN Email END) AS Email_1  
	   ,MAX(CASE WHEN RNum_Email=2 THEN Email END) AS Email_2  
       ,STUFF((SELECT DISTINCT',' + Email FROM CTE C1 WHERE RNum_Email > 2 AND C1.LastName=C.LastName AND C1.FirstName=C.FirstName  
               FOR XML PATH('')),1,1,'') AS Email_3  
       ,MAX(CASE WHEN RNum_PhoneNumber=1 THEN PhoneNumber END) AS PhoneNumber_1  
	   ,MAX(CASE WHEN RNum_PhoneNumber=2 THEN PhoneNumber END) AS PhoneNumber_2  
       ,STUFF((SELECT DISTINCT',' + PhoneNumber FROM CTE C2 WHERE RNum_PhoneNumber > 2 AND C2.LastName=C.LastName AND C2.FirstName=C.FirstName  
               FOR XML PATH('')),1,1,'') AS PhoneNumber_3  
 FROM CTE C  
 GROUP BY FirstName,LastName,Address,City,State,Zip  
)  
SELECT * FROM CTE2 --Check IF Correct Before Update  
--UPDATE CTE2 SET Column...=Column...

Best regards,
LiHong

Debilon 431

Also To Update tables from the CTE, i need to change the select statement to include all fields in the actual table

SELECT FirstName,LastName,Address,City,State,Zip  -- > Should be changed to   Select *

Then while running all the columns not part of the 'GROUP BY' clause will cause the query to fail.

Column 'CTE.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

My Set Statement should look like this:

Update CTE2
 Set 
 EMail        = email_1,
 Email2       = Email_2,
 Email3       = Email_3,
 PhoneNumber    = PhoneNumber_1,
 PhoneNumber2   = PhoneNumber_2,
 PhoneNumber3   = PhoneNumber_3

LiHong-MSFT 10,056

Hi @Debilon
We can't update table #PhoneTable,because a single person in the source table has one or more rows, but a single person in the result table has only one row. In other words, aggregate functions are used in CTE2.
Why not insert the queried data into a new table?Like this:

;WITH CTE AS  
(  
 SELECT *,DENSE_RANK()OVER(PARTITION BY FirstName, LastName,Address,City,State,Zip ORDER BY ISNULL(email,'')DESC) AS RNum_Email  
         ,DENSE_RANK()OVER(PARTITION BY FirstName, LastName,Address,City,State,Zip ORDER BY ISNULL(PhoneNumber,'')DESC) AS RNum_PhoneNumber  
 FROM #PhoneTable  
),CTE2 AS   
(   
<select statement>  
)  
SELECT * into #New_PhoneTable FROM CTE2

Best regards,
LiHong

Answer 2

Naomi Nosonovsky 8,431

This seems to work, but I have to order by email in both cases, otherwise I got an error:

DECLARE @PhoneTable Table (ID INT IDENTITY PRIMARY KEY, Email VARCHAR(30),FirstName VARCHAR(30), LastName Varchar(30), Address varchar(50), City Varchar(30), State Varchar(10), Zip Varchar(10),
  PhoneNumber VARCHAR(30));



  INSERT INTO @PhoneTable (Email, FirstName, LastName,Address,City,State,Zip,PhoneNumber) VALUES
  (Null,' Darla', 'Shmooz', '81 Grass Valley DR', 'New York', 'NY', '10021', '829-514-7725'),
  ('******@gmail.com',' Darla', 'Shmooz', '81 Grass Valley DR', 'New York', 'NY', '10021', '829-514-7725'),
  (Null,' Darla', 'Shmooz', '81 Grass Valley DR', 'New York', 'NY', '10021', '829-914-8825')

  ;WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY FirstName, LastName,Address,City,State,Zip
  ORDER BY CASE WHEN email IS NULL THEN 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZ' ELSE email END) AS RnEmail,
  ROW_NUMBER() OVER (PARTITION BY FirstName, LastName,Address,City,State,Zip
  ORDER BY CASE WHEN phoneNumber IS NULL THEN 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZ' ELSE phoneNumber END) AS RnPhone
  FROM @PhoneTable)

  SELECT FirstName, LastName,Address,City,State,Zip, 
  MAX(CASE WHEN RnEmail = 1 THEN Email END) AS Email1,
  MAX(CASE WHEN RnEmail = 2 THEN Email END) AS Email2,
  STRING_AGG(CASE WHEN RnEmail>2 THEN Email END, ',' ) WITHIN GROUP (ORDER BY RnEmail) AS Email3,
  MAX(CASE WHEN RnPhone = 1 THEN PhoneNumber END) AS Phone1,
  MAX(CASE WHEN RnPhone = 2 THEN PhoneNumber END) AS Phone2,
  STRING_AGG(CASE WHEN RnPhone>2 THEN PhoneNumber END, ',' ) WITHIN GROUP (ORDER BY RnEmail) AS Phone3
  FROM cte
  GROUP BY FirstName, LastName,Address,City,State,Zip

Debilon 431 Reputation points

2022-04-26T18:24:01.73+00:00

if two phone number / emails are the same, they need to be added only once
currently phone 1 and phone 2 are the same.
Naomi Nosonovsky 8,431 Reputation points

2022-04-26T18:32:29.387+00:00

Sorry, use dense_rank instead of ROW_NUMBER(). I was thinking DENSE_RANK() but wrote ROW_NUMBER() out of habit. Although it would not prevent aggregating same phones after the 3rd :( Not exactly sure then for now...
Naomi Nosonovsky 8,431 Reputation points

2022-04-26T18:53:16.497+00:00

If we need distinct phones and numbers after 3rd, then we would have to use two CTE (or temp table) - first with distinct info by email, second with distinct into for phone, split and aggregate separately, then join back together based on the common columns. Same idea as my query, but split into 2 separate.
Debilon 431 Reputation points

2022-04-26T23:49:19.177+00:00
So After cracking my head open for about 5 hours i discovered two things:

No STRING_AGG in an update - It is because of the order that SQL executes the query. To get the data, it will need to aggregate, meaning you no longer have the original rows to update, so it fails

No Group By in an update - More or less the same reason as number 1.

Share via

Combaine rows into one row with partial information match

1 additional answer

Your answer