CODEWITHSUNDEEP
sqlsql-server
Sai
Sai

Reputation: 97

SELECT SQL based on one Condition in Column Value

Can someone help me with the SQL query using the below data?enter image description here

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

Upvotes: 0

Views: 1221

Answers (3)

paparazzo
paparazzo

Reputation: 45096

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;

Upvotes: 1

Imre
Imre

Reputation: 504

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'

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

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;

Upvotes: 0

Related Questions