CODEWITHSUNDEEP
sqlt-sql
TKESuperDave
TKESuperDave

Reputation: 73

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 (one home and the other bussiness) 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.

Upvotes: 3

Views: 444

Answers (1)

HotblackDesiato
HotblackDesiato

Reputation: 348

I agree that you need to join the table to itself, but you also need a left join to not exclude single addresses and I've added a row id to eliminate duplicate rows.

WITH Address AS
(
    SELECT
     *
    ,ROW_NUMBER() OVER(PARTITION BY AccountNum ORDER BY AccountNum) AS RowID
    FROM AddressTable
)
SELECT
 a1.AccountNum
,a1.Name
,a1.Contact
,a1.AddressLine1
,a1.City
,a1.County
,a1.State
,a1.Zip
,a1.AddrType
,a1.IndivOrBuss
,a2.AddressLine1 AS AddressLine2
,a2.City AS [2ndCity]
,a2.County AS [2ndCounty]
,a2.State AS [2ndState]
,a2.Zip AS [2ndZip]
,a2.AddrType AS [2NDAddrType]
FROM Address a1
LEFT JOIN Address a2
ON a1.AccountNum=a2.AccountNum
AND (a1.AddressLine1!=a2.AddressLine1
        OR a1.City!=a2.City
        OR a1.County!=a2.County
        OR a1.State!=a2.State
        OR a1.Zip!=a2.Zip
        OR a1.AddrType!=a2.AddrType)
WHERE a1.RowID=1;

Upvotes: 2

Related Questions