how to combine the two query

Question

 My query as follows

 

 

select Av.Associate_ID as Associateid,Av.IsActive,CAD.value,CAD.ValidateFlag,CGA.facility

from

[CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] Av

inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD

ON CAD.[Associate_ID]=Av.[Associate_ID]

inner join [CentralRepository].[dbo].[vw_CentralRepository_GSMS_AssociateSeatInfo] CGA

ON CGA.[Associate_ID]=Av.[Associate_ID]

where CGA.[Associate_ID] = '101012'(select

"Address" = LTRIM(RTRIM((LTRIM(RTRIM(CAA.Address1)) + ', '+

CASE WHEN LTRIM(RTRIM(CAA.Address2))!='' THEN LTRIM(RTRIM(CAA.Address2)) + ', ' ELSE '' END +

CASE WHEN LTRIM(RTRIM(CAA.Address3))!='' THEN LTRIM(RTRIM(CAA.Address3)) + ', ' ELSE '' END+

LTRIM(RTRIM((COALESCE(replace(CAA.address4,' ',''),'')+ CASE WHEN COALESCE(caa.Address4,'') !='' THEN + ', '+

CASE WHEN COALESCE(RTRIM(CAA.city),'')!='' THEN COALESCE(RTRIM(CAA.city),'') END + ' '+ ', '+

CASE WHEN COALESCE(RTRIM(CAA.Pincode),'')!='' THEN COALESCE(RTRIM(CAA.Pincode),'') ELSE '' END

ELSE COALESCE(RTRIM(CAA.city),'')+', '+COALESCE(RTRIM(CAA.Pincode),'') END )))))) FROM

[OneC_988].[dbo].[988_Details_AssociateProfile] AP

LEFT JOIN [CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] CAD

ON CAD.[Associate_ID]=AP.[varchar_AssociateID]

LEFT JOIN [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CONT

ON CONT.Associate_ID=AP.[varchar_AssociateID] AND CONT.Contact_Type ='MBL1'-- AND CONT.ValidateFlag = 'S'

LEFT OUTER JOIN [CentralRepository].[dbo].[vw_CentralRepository_GSMS_AssociateSeatInfo] CGA

ON CGA.[Associate_ID]=AP.[varchar_AssociateID]

LEFT JOIN CentralRepository..vw_CentralRepository_AssociateAddress CAA

ON CAA.Associate_ID=AP.[varchar_AssociateID]

AND AddressType IN (SELECT CASE WHEN COALESCE(COUNT(Associate_ID),0) >= 1 THEN 'Mail' ELSE 'Home' END

FROM CentralRepository..vw_CentralRepository_AssociateAddress))

 

When i run the above query shows output as follows

 

Assoicate     IsActive         Value                  Validateflag           Facility

101012           A              984/123-4567              S                     CHN - NVL 

 

Address

Street

 

 

 

Using above query i want output  as follows

 

Assoicate          IsActive           Value              Validateflag          Facility               Address

101012                A               984/123-4567         S                    CHN - NVL           Street

for getting a above output how can i do.