How about this?create table #vendor ( VendorName varchar(20) , Address1 varchar(20) , Address2 varchar(20))insert #vendor values ('A', 'A', 'c/o')insert #vendor values ('B', null, 'c/o')insert #vendor values ('C', 'B', null)insert #vendor values ('D', null, 'c/o')insert #vendor values ('E', null, 'c/o')select * ,Rules = Substring(Cast( Case When VendorName = Address1 then ',1' else '' end + Case When Address1 = 'c/o' or Address2 = 'c/o' then ',2' else '' end + Case When VendorName is not null and Address1 is null then ',3' else '' end as Varchar(31)), 2, 30)from #vendordrop table #vendor-- here is the outputVendorName Address1 Address2 Rules --------------- --------------- --------------- -------------------- A A c/o 1,2B NULL c/o 2,3C B NULL D NULL c/o 2,3E NULL c/o 2,3