Thursday, 22 August 2013

T-SQL Merging Rows if ID is same and retaining unique information for each row

T-SQL Merging Rows if ID is same and retaining unique information for each
row

I have a table that pulls the name and addresses of individuals and
organizations. Sometimes in this table a individual will have 2 addresses
and thus come back twice. Other times they could only have one address.
AccountNum Name Contact AddressLine1 City County
State Zip AddrType IndivOrBuss
321 Dilbert Law Mr.Dilbert 123 Merrwood Pittsburgh NULL
PA 15212 Home Business
321 Dilbert Law Mr.Dilbert 321 Dogbert Dr. Pittsburgh NULL
PA 15212 Bussiness Business
I have to take this table and make it so that it fits in another table
while the keeping both addresses and having only one row per AccountNum.
The rows will need to be merged to show both addresses and unique fields.
It should look something like this:
AccountNum Name Contact AddressLine1 City
County State Zip AddrType IndivOrBuss Address2Line1
2ndCity 2ndCounty 2ndState 2ndZip 2ndAddrTyp
321 Dilbert Law Mr.Dilbert 123 Merrwood Pittsburgh NULL
PA 15212 Home Business 321 Dogbert Dr.
Pittsburgh NULL PA 15212 Bussiness
I'm unsure as to how to approach the merge while retaining those that
don't need to be merged.
I've already pulled the ones that need merged by using
FROM Address WHERE Address.[AccountNum] IN
(Select Address.[AccountNum]
From Address
Group by Address.[AccountNum]
having count(*)>1);
Which I'm sure is not the best way to do to find the duplicates for a
merging back into the other row. I'd appreciate any ideas.

No comments:

Post a Comment