Case statement Help needed

Hi

Try this

select coalesce(com.p_Id,nc.P_Id),
       isnull(com.LineofBusiness,'') +
       case when com.LineofBusiness is not null and nc.LineofBusiness is not null then ' & ' else '' end +
       isnull(nc.LineofBusiness,'')   
From
(select P_Id, 'Commercial' as LineofBusiness
from pg_test.Test
where LineofBusiness = 'Comm'
Group by P_Id, LineofBusiness) com

Full outer join
(
select P_Id, 'Non Commercial' as LineofBusiness
from pg_test.Test
where LineofBusiness = 'Non Comm'
Group by P_Id, LineofBusiness
) nc
on nc.P_Id = com.P_Id