Value lookup in multiple columns in a table in given row

Question

I'm looking to achieve my requirement. to define a ClientService1 based on the other columns lookup in table. In below table if Service1, Service2, Service3, Service4 either one column has value ABC and if all remaining columns null or it should not contain other values, then I'll call that row eligible for ClientService1

Above scenario is eligible for ClientService1.

Above scenario is not eligible for ClientService1. Bcz service1 row has CGF

ClientService2 In below table if Service1, Service2, Service3, Service4 either one column has values ABC, DEF and if all remaining columns null or it should not contain other values, then I'll call that row eligible for ClientService2

Above scenario is eligible for ClientService2.

Above scenario is not eligible for ClientService2. Bcz service3 column has XYZ

How can I write SQL query to achieve ClientService1 & ClientService2 requirements

Answer 1

Hi @satish nuthpally

Maybe you can concat all the field ,then do a filter

Best Regards，
Isabella

Answer 2

Check a preliminary approach:

-- Eligible rows for ClientService1  
select *  
from MyTable  
where  
    (select count(c) from   
        (select Service1 union select Service2 union select Service3 union select Service4 except select 'ABC') t(c)) = 0  
    and  
    (select count(c) from   
        (select Service1 union select Service2 union select Service3 union select Service4 intersect select 'ABC') t(c)) = 1  
  
-- Eligible rows for ClientService2  
select *  
from MyTable  
where  
    (select count(c) from   
        (select Service1 union select Service2 union select Service3 union select Service4 except (select 'ABC' union select 'DEF')) t(c)) = 0  
    and  
    (select count(c) from   
        (select Service1 union select Service2 union select Service3 union select Service4 intersect (select 'ABC' union select 'DEF')) t(c)) = 2  

Answer 3

You can try this once:

create table testservice(service1 varchar(100),service2 varchar(100),service3 varchar(100),service4 varchar(100))  
  
insert into testservice (service2) values ('ABC');  
insert into testservice (service2,service1) values ('ABC','CGF');  
insert into testservice (service2,service1) values ('ABC','DEF');  
insert into testservice (service2,service1,service3) values ('ABC','DEF','XYZ');  
  
SELECT   
CASE WHEN 'ABC' in (service1,service2,service3,service4)   
AND (ISNUMERIC(ISNULL(Service1,1)) + ISNUMERIC(ISNULL(Service2,1))   
+ ISNUMERIC(ISNULL(Service3,1)) + ISNUMERIC(ISNULL(service4,1)) = 3) THEN 'ClientService1'   
WHEN 'ABC' in (service1,service2,service3,service4) and 'DEF' in (service1,service2,service3,service4)   
AND (ISNUMERIC(ISNULL(Service1,1)) + ISNUMERIC(ISNULL(Service2,1))   
+ ISNUMERIC(ISNULL(Service3,1)) + ISNUMERIC(ISNULL(service4,1)) = 2)  
THEN 'ClientService2' END  
FROM testservice;