Below code is to demonstrate the working which can be performed on the original query

DECLARE @SubQuery1 Table (

LoadNumbervarchar(20)

,CUSTOMERvarchar(20)

,PickPalletCountint

)

DECLARE @SubQuery2 TABLE(

LoadNumbervarchar(20)

,CUSTOMERvarchar(20)

,con_countINT

)

INSERT INTO @SubQuery1

Select 'AL75152','CD113', 2 UNION ALL

Select 'AL75152','GI005', 3

INSERT INTO @SubQuery2

SELECT 'AL75152','AS029', 21

SELECT X.LoadNumber, X.CUSTOMER1, X.PickPalletCount

, CASE WHEN X.ROWNUM = 1 THEN X.CUSTOMER2 ELSE NULL END AS CUSTOMER2

, CASE WHEN X.ROWNUM = 1 THEN X.con_count ELSE NULL END AS con_count

FROM (

SELECT A.LoadNumber, A.CUSTOMER AS CUSTOMER1, A.PickPalletCount, B.CUSTOMER AS CUSTOMER2

, B.con_count

, ROW_NUMBER() OVER (PARTITION BY A.LoadNumber, B.LoadNumber ORDER BY A.LoadNumber) ROWNUM

FROM @SubQuery1 A

JOIN @SubQuery2 B ON A.LoadNumber = B.LoadNumber

) X

Hope it helps.