select max(Contract_Num) as contract_num, max(Lic_Plate_Num) as lic_plate_num, max(cast(Revision_Eff_Date as date)) as rev_eff_date, max(contract_name) as cn_name, max(CASE WHEN product_code = 'CSL' THEN Limit_1 END) as value_1 , max(CASE WHEN c.product_code in ('PIP','FPB') THEN Limit_1 END) as value_2, max(CASE WHEN product_code = 'MEDPM' THEN Limit_1 END) as value_3, max(CASE WHEN c.product_code in ( 'UMUIM','UMISG','UMCSL') THEN Limit_1 END) as value_4, max(CASE WHEN c.product_code in ( 'UNCSL','UNDSG') THEN Limit_1 END) as value_5 from [dba].[Contract] as a left join [dba].[CDAA_Vehicle] as b on a.Contract_ID=b.Contract_ID left join [dba].[Contract_Product] as c on c.Contract_ID=a.Contract_ID left join dba.Contract_Name as d on c.Contract_ID = d.Contract_ID left join dba.Contract_Article ca on c.Contract_ID = ca.Contract_ID where ca.Status!='D'
and the output is
contract_num lic_plate_num rev_eff_date cn_name value_1 value_2 value_3 value_4 value_5 1231 V24B32 12-09-2022 test null null 100 200 367
This is my query and output, here I'm selecting limit_1 value based on product and the limit_1 value may or may not be null. but here I want to print one message 'Included' for individual product limit_1, when the limit_1 value is not null.