How To Merge Two Data Records into One?

[lrdave36]

Hey guys,

I'm dealing with a problem that really is a result of poor table design. It's a simple address table, but instead of creating a field for address line 2, the table contains a second record for the same business.

Ex.

NAME              ADDRESS       CITY   STATE ZIP  LINE NUMBER

REP HOUSE      121 WASHINGTON   DALLAS   TX  75217    1
REP HOUSE          SUITE 123A   DALLAS   TX  75217    2

This is a problem for me because I'm importing this data into Word for envelope printing, and it would print out two envelopes.

Is there some way I can merge the two records into a single one with a new field called address line 2?

 

[gmmastros]

I assume you have some sort of AddressId column that you are not showing. You can write a left join query that pulls the data from line 1 and line 2 but on the same row. Something like this:

Select A.Name, 
       A.Address,
       Coalesce(B.Address, '') As Address2,
       A.City,
       A.State,
       A.Zip
From   Address As A
       Left Join Address As B
         On A.AddressId = B.AddressId
         And A.LineNumber = 1
         And B.LineNumber = 2

* Not tested

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom