Big Kudos!!! You are new around here and you posted everything so nicely. This makes it simple and easy to work on the issue. As Luis said, the articles are crosstabs are a gold mine. One thing that can be a little difficult to figure out is how to make it work for a situation like yours. At first glance you don't have anything to aggregate on so you probably sit there scratching your head wondering how to make this work.

You did such an awesome job posting the details I couldn't help myself showing you the code to do this in a static type of situation.

with MySortedData as

(

select ca.companyID,

ca.CompanyAddress,

ca.CompanyState,

cc.CompanyContact,

ROW_NUMBER()over(partition by ca.CompanyID order by cc.CompanyContact) as RowNum

from @CompanyAddress ca

join @CompanyContact cc on cc.companyID = ca.companyID

)

select companyID, CompanyAddress, CompanyState,

max(case when RowNum = 1 then CompanyContact end) as CompanyContact1,

max(case when RowNum = 2 then CompanyContact end) as CompanyContact2,

max(case when RowNum = 3 then CompanyContact end) as CompanyContact3

from MySortedData

group by companyID, CompanyAddress, CompanyState

If you need a more dynamic approach (you don't know how many columns you need to end up with) you will need to reference the article about dynamic cross tabs. You can find that reference in Luis' post or in my signature.

Hope this helps.