• I don't think this is the fastest solution, but it will work:

    Select * from #Temp1

    Select * from #Customer_Profile_Master1

    Update #Customer_Profile_Master1

    set Co_apcode_1_categ = (select top 1 Category from #temp1 where rownumber = 1 and cpm.lnno = lnno)

    , Co_apcode_2_categ = (select top 1 Category from #temp1 where rownumber = 2 and cpm.lnno = lnno)

    , Co_apcode_3_categ = (select top 1 Category from #temp1 where rownumber = 3 and cpm.lnno = lnno)

    , Co_apcode_4_categ = (select top 1 Category from #temp1 where rownumber = 4 and cpm.lnno = lnno)

    from #Customer_Profile_Master1 cpm

    Select * from #Customer_Profile_Master1

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **