combining ppl at the same address into 1 record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

combining ppl at the same address into 1 record

Author

Topic

ravl13
Starting Member

38 Posts

Posted - 2011-10-05 : 13:25:38

Greetings,

I have a temporary table (lets call it #tmptable), and in it are the following columns: FirstName, LastName, Address1, Address2, City, State, Zip

I would like to combine records of people who have the same lastname and address information into one record. So, if any records are identical except for the first name, I would like all firstnames for those matching records concatenated into the firstname column.

Here's some partial example data of what I'd like to achieve:

Before running a SQL statement on #tmptable

FIRST NAME, LAST NAME, ADDRESS1, CITY, ZIP

Stephen, Rubin, 23 Hall Road, Chelmsford, 01824
Sharon, Rubin, 23 Hall Road, Chelmsford, 01824
Marilyn, Rubin, 23 Hall Road, Chelmsford, 01824
Katie, Gill, 34 Hall Road, Chelmsford, 01824
Pat, Rowe, 52 Parker Road, Chelmsford, 01824

Contents of #tmptable After running the statement

FIRST NAME, LAST NAME, ADDRESS1, CITY, ZIP

Stephen & Sharon & Marilyn, Rubin, 23 Hall Road, Chelmsford, 01824
Katie, Gill, 34 Hall Road, Chelmsford, 01824
Pat, Rowe, 52 Parker Road, Chelmsford, 01824

How could I accomplish this with a SQL statement?

**Apologies for the terrible data formatting. I don't know how to make it look neat on forums**

Thanks,
-ravl13

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 01:01:33

[code]
SELECT STUFF((SELECT '&' + FIRSTNAME FROM #tmptable WHERE ADDRESS1=t.ADDRESS1 AND CITY=t.CITY AND ZIP=t.ZIP FOR XML PATH('')),1,1,''),
STUFF((SELECT DISTINCT '&' + LASTNAME FROM #tmptable WHERE ADDRESS1=t.ADDRESS1 AND CITY=t.CITY AND ZIP=t.ZIP FOR XML PATH('')),1,1,''),
ADDRESS1,
CITY,
ZIP
FROM #tmptable t
[/code]

you can use the below in an update if you want to get results on tmptable itself.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

m_imran18
Starting Member

14 Posts

Posted - 2011-10-06 : 03:06:14

Select
Replace(Stuff((Select '& ' +[FirstName]+ ' ' from #tmptable B
Where A.[Address1]=B.[Address1] AND A.CITY=B.CITY AND A.ZIP=B.ZIP for xml path('') ),1,1,''),'amp;','') as [First Name],
Replace(Stuff((Select '& ' +[LastName]+ ' ' from #tmptable B
Where A.[Address1]=B.[Address1] AND A.CITY=B.CITY AND A.ZIP=B.ZIP Group By [Lastname] for xml path('') ),1,1,''),'amp;','') as [Last Name] ,
ADDRESS1,
CITY,
ZIP
from #tmptable A
group By ADDRESS1,CITY,ZIP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 05:04:11

quote:
Originally posted by m_imran18

Select
Replace(Stuff((Select '& ' +[FirstName]+ ' ' from #tmptable B
Where A.[Address1]=B.[Address1] AND A.CITY=B.CITY AND A.ZIP=B.ZIP for xml path('') ),1,1,''),'amp;','') as [First Name],
Replace(Stuff((Select '& ' +[LastName]+ ' ' from #tmptable B
Where A.[Address1]=B.[Address1] AND A.CITY=B.CITY AND A.ZIP=B.ZIP Group By [Lastname] for xml path('') ),1,1,''),'amp;','') as [Last Name] ,
ADDRESS1,
CITY,
ZIP
from #tmptable A
group By ADDRESS1,CITY,ZIP

why you need extra replace? STUFF already does the replace
Please try to post something new rather than restating whats already posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

m_imran18
Starting Member

14 Posts

Posted - 2011-10-06 : 05:25:24

If I donot replace it will look like.
Before replace : amp;Stephen&Sharon&Marilyn
After replace : Stephen & Sharon & Marilyn

Thank you.

ravl13
Starting Member

38 Posts

Posted - 2011-10-06 : 13:43:10

I created a test #tmptable, with the full address information. Its columns look like this:

[FirstName]
,[LastName]
,[Address1]
,[Address2]
,[Address3]
,[City]
,[State]
,[Zip]

This is an image of what the data in the table looks like:

Now I tried modifying both m_imran's code and visakh's code to account for my additional columns, but both sets of code turn the FirstName and LastName columns to NULL. Here is my code and output for both posters:

---------------------------

Visakh's code


SELECT STUFF((SELECT '&' + FIRSTNAME FROM #tmptable WHERE ADDRESS1=t.ADDRESS1 AND CITY=t.CITY AND ZIP=t.ZIP and address2=t.address2 and address3=t.address3 and state = t.state FOR XML PATH('')),1,1,''),
STUFF((SELECT DISTINCT '&' + LASTNAME FROM #tmptable WHERE ADDRESS1=t.ADDRESS1 AND CITY=t.CITY AND ZIP=t.ZIP and address2=t.address2 and address3=t.address3 and state = t.state FOR XML PATH('')),1,1,''),
ADDRESS1, address2, address3, CITY, state, ZIP  
FROM #tmptable t

Visakh's output:

**Visakh's code doesn't seem to combine the records**

-------------------------------------------

m_inran's code


Select
Replace(Stuff((Select '& ' +[FirstName]+ ' ' from #tmptable B
Where A.[Address1]=B.[Address1] AND A.CITY=B.CITY AND A.ZIP=B.ZIP and A.address2=B.address2 and A.address3=B.address3 and A.state = B.state for xml path('') ),1,1,''),'amp;','') as [First Name],
Replace(Stuff((Select '& ' +[LastName]+ ' ' from #tmptable B
Where A.[Address1]=B.[Address1] AND A.CITY=B.CITY AND A.ZIP=B.ZIP and A.address2=B.address2 and A.address3=B.address3 and A.state = B.state
Group By [Lastname] for xml path('') ),1,1,''),'amp;','') as [Last Name] ,ADDRESS1, Address2, Address3, CITY, State,ZIP
from #tmptable A
group By ADDRESS1, Address2, Address3, CITY, State, ZIP

m_inran's output

Any ideas on why this is happening?

-ravl13

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 13:53:29

thats because of all NULL values in other fields which will not select data using = operator. if you've NULL values in selects inside make conditions like


....
Replace(Stuff((Select '& ' +[FirstName]+ ' ' from #tmptable B
Where COALESCE(A.[Address1],'')=COALESCE(B.[Address1],'') 
AND COALESCE(A.CITY,'')=COALESCE(B.CITY,'') 
AND COALESCE(A.ZIP,'')=COALESCE(B.ZIP,'') 
and COALESCE(A.address2,'')=COALESCE(B.address2,'') 
and COALESCE(A.address3,'')=COALESCE(B.address3,'') 
and COALESCE(A.state,'') = COALESCE(B.state,'') 
for xml path('') ),1,1,''),'amp;','') as
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-06 : 13:55:54

Maybe this?

SELECT 
    STUFF((SELECT '&' + FIRSTNAME FROM #tmptable WHERE LastName = t.LastName AND ADDRESS1=t.ADDRESS1 AND CITY=t.CITY AND ZIP=t.ZIP FOR XML PATH('')),1,1,''),
    LASTNAME,
    ADDRESS1,
    CITY,
    ZIP
FROM #tmptable t
GROUP BY
    LASTNAME,
    ADDRESS1,
    CITY,
    ZIP

If not, post soem sample data in a consumable format:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ravl13
Starting Member

38 Posts

Posted - 2011-10-06 : 15:27:46

Lamprey, your solution works as long as there is no NULLs, but if there are NULL values, it gets screwed up.

Visakh, thank you for your suggestion. I tested it and it works like I wanted. It's a lot of typing, and I appreciate your time on it :)

Here's the satisfactory code:


Select
Replace(Stuff((Select '& ' +[FirstName]+ ' ' from #tmptable B
Where coalesce(A.[Address1],'')=coalesce(B.[Address1],'') AND coalesce(A.CITY,'')=coalesce(B.CITY,'') AND coalesce(A.ZIP,'')=coalesce(B.ZIP,'') and coalesce(A.address2,'')=coalesce(B.address2,'') and coalesce(A.address3,'')=coalesce(B.address3,'') and coalesce(A.state,'') = coalesce(B.state,'') for xml path('') ),1,1,''),'amp;','') as [First Name],
Replace(Stuff((Select '& ' +[LastName]+ ' ' from #tmptable B
Where coalesce(A.[Address1],'')=coalesce(B.[Address1],'') AND coalesce(A.CITY,'')=coalesce(B.CITY,'') AND coalesce(A.ZIP,'')=coalesce(B.ZIP,'') and coalesce(A.address2,'')=coalesce(B.address2,'') and coalesce(A.address3,'')=coalesce(B.address3,'') and coalesce(A.state,'') = coalesce(B.state,'')  
Group By [Lastname] for xml path('') ),1,1,''),'amp;','') as [Last Name] ,ADDRESS1, Address2, Address3, CITY, State,ZIP
from #tmptable A
group By ADDRESS1, Address2, Address3, CITY, State, ZIP

And thanks to imran for providing the base code to start from.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 01:34:03

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources