SELECT SQL based on one Condition in Column Value

Question

Can someone help me with the SQL query using the below data?

Whenever I see the type as deactivating irrespective of other types in same-ID I have to get only deactivate values and the rest of values should be null.

select     id,     
pid,    
type,    
case when type='deactivate' then null as value1 else value1 end,     
case when type='deactivate' then  null as value2 else value2 end,     
case when type='deactivate' then  null as value3 else value3 end,     
case when type='deactivate' then null as value4 else value4 end,     
case when type='deactivate' then null as value5 else value5 end,     
case when type='deactivate' then value6 end,     
case when type='deactivate' then deactivate_date end,     
case when type='deactivate' then status end     
from typetable

But whenever the pid changes I am getting values in value1 to 5. Is there any way in my data whenever I see type='deactivate' I have to ignore the values from value1 to 5 for all the types in the same ID? The output should be like nulls for ID- 1 & 3 mentioned below.

id  pid type    value1  value2  value3  value4  value5  value6  deactive date   Status
0   1   case    99  null    null    null    null    null    null    
0   2   test    null    101 null    null    null    null    null    
0   3   levels  null    null    CAAD    null    null    null    null    
0   4   package null    null    null    null    DSIT    null    null    

1   1   case    null    null    null    null    null    null    null    
1   2   test    null    null    null    null    null    null    null    
1   3   levels  null    null    null    null    null    null    null    
1   4   package null    null    null    null    null    null    null    
1   5   deactivate  null    null    null    null    null    9999999 9/1/2014    Void ID

2   1   case    CLR 6#  null    null    null    null    null    null    
2   2   test    null    6   null    null    null    null    null    
2   3   levels  null    null    MAA null    null    null    null    

3   1   case    null    null    null    null    null    null    null    
3   2   test    null    null    null    null    null    null    null    
3   3   levels  null    null    null    null    null    null    null    
3   4   package null    null    null    null    null    null    null    
3   5   deactivate  null    null    null    null    null    9999999 1/7/2016    Closed

there are values under each type only when i get the type deactivate simply make null as values from 1 to 5 if type is NOT 'DEACTIVATE' then i have to get all the values — Sai
– Sai, Commented Feb 23, 2018 at 18:47
The question is quite unclear really: here's what I am understanding. For each id, if there is any row with the same id and type deactivate then hide all values for the entire group. — shawnt00
– shawnt00, Commented Feb 23, 2018 at 18:58

paparazzo · Accepted Answer · 2018-02-23 20:33:45Z

1

declare @t table (id int, pid int, col1 varchar(10), col2 varchar(10)) 
insert into @t 
       values (1, 1, 'askjdf', 'laskjdf')
            , (1, 2, 'asksfjdf', 'ljdf')
            , (2, 1, 'askjdf', 'laskjdf')
            , (2, 2, 'deact', 'laskjdf');

select t.* 
from @t t 
where not exists (select 1 from @t tm where tm.id = t.id and tm.col1 = 'deact') 
union all 
select t.id, t.pid, t.col1, null  
from @t t 
where     exists (select 1 from @t tm where tm.id = t.id and tm.col1 = 'deact') 
order by id, pid;

answered Feb 23, 2018 at 20:33

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shawnt00 · Accepted Answer · 2018-02-23 18:57:51Z

0

Something like this would work:

select
    id, pid,
    case when masked = 0 then value 1 else null end as value1,
    ...
from <table> t cross apply (
    select max(case when t2.type = 'deactivate' then 1 else 0 end)
    from <table> t2
    where t2.id = t.id
) m(masked)

Similar, and probably better:

with data as (
    select *,
        max(case when type = 'deactivate' then 1 else 0 end)
            over (partition by id) as masked
    from <table>
)
select
    id, pid,
    case when masked = 0 then value 1 else null end as value1,
    ...
from data;

edited Feb 23, 2018 at 18:57

answered Feb 23, 2018 at 18:51

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Comments

Imre · Accepted Answer · 2018-02-23 19:23:00Z

0

Cleanest and easiest if you make a self left join on id AND type='deactivate' and check the joining table's type field rather than the left table's type field:

select t1.id,t1.pid,t1.type,
case when t2.type='deactivate' then null else t1.value1 end AS value1,
case when t2.type='deactivate' then null else t1.value2 end AS value2, 
case when t2.type='deactivate' then null else t1.value3 end AS value3, 
case when t2.type='deactivate' then null else t1.value4 end AS value4, 
case when t2.type='deactivate' then null else t1.value5 end AS value5, 
case when t2.type='deactivate' then t1.value6 end AS value6, 
case when t2.type='deactivate' then t1.deactivate_date end AS deactivate_date, 
case when t2.type='deactivate' then t1.status end from typetable t1 
LEFT JOIN typetable t2 ON t1.id=t2.id AND t2.type='deactivate'

edited Feb 23, 2018 at 19:23

answered Feb 23, 2018 at 19:16

Imre

6041 gold badge6 silver badges12 bronze badges

2 Comments

Sai Over a year ago

I have tried this query but when the type changes the values are coming from value1 to 5 in case id 1 and 3

Imre Over a year ago

@Sai you must be doing something wrong or your data isn't as in the screenshot. This query will result in null values accross columns value1-5 for id=1 regardless the type.

Collectives™ on Stack Overflow

SELECT SQL based on one Condition in Column Value

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related