CODEWITHSUNDEEP
sqlsql-server
Rick
Rick

Reputation: 1569

SQL function to show values based on other columns

I have a table that has 5 columns. Person ID, C11,c12,c13,c14. So I want to write a query that selects Person ID, select C11 c12 c13 c14 only if all four columns have value. If C11 is present and C12 is null then show all the columns null.

Below is the restester link where I have a sample query.

https://rextester.com/YJG20854

any help?!

Upvotes: 0

Views: 63

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

One solution uses case:

select person_id,
       (case when not (c11 is null or c12 is null or c13 is null or c14 is null)
             then c11 end),
       (case when not (c11 is null or c12 is null or c13 is null or c14 is null)
             then c12 end),
       (case when not (c11 is null or c12 is null or c13 is null or c14 is null)
             then c13 end ),
       (case when not (c11 is null or c12 is null or c13 is null or c14 is null)
             then c14 end)
from temp1;

This is just a slight variation on your query, with some syntax issues fixed.

A more clever method uses left join:

select t1.person_id, null1.cl1, null1.cl2, null1.cl3, null1.cl4
from temp1 t1 left outer join
     temp1 null1
     on t1.person_id = null1.person_id and
        not (t1.c11 is null or t1.c12 is null or t1.c13 is null or t1.c14 is null);

Or uses cross apply:

select t1.person_id, null1.*
from temp1 t1 cross apply
     (select top (1) c11, c12, c13, c14
      from (values (c11, c12, c13, c14, 1), (null, null, null, null, 0)) v(c11, c12, c13, c14, ord)
      order by (case when c11 is null or c12 is null or c13 is null or c14 is null then 1 else 0 end), ord
     ) null1;

Upvotes: 4

Vishnu Kunchur
Vishnu Kunchur

Reputation: 1726

SELECT * FROM temp1
WHERE c11 + c12 + c13 + c14 IS NOT NULL

UNION

SELECT person_id, NULL AS c11, NULL AS c12, NULL AS c13, NULL AS c14 
FROM temp1
WHERE c11 IS NOT NULL AND c12 IS NULL

Output:

1005  NULL NULL NULL NULL           
1001  NULL NULL NULL NULL           
1002     1    1    3    4

Upvotes: 1

DanB
DanB

Reputation: 2124

You can do this :

select person_id,
    case when c11 + c12 + c13 + c14 is not null then c11 else null end c11,
    case when c11 + c12 + c13 + c14 is not null then c12 else null end c12,
    case when c11 + c12 + c13 + c14 is not null then c13 else null end c13,
    case when c11 + c12 + c13 + c14 is not null then c14 else null end c14
from person

Or this :

select person_id,c11,c12,c13,c14
from person
where c11 + c12 + c13 + c14 is not null
union
select person_id,null,null,null,null
from person
where c11 + c12 + c13 + c14 is null

Upvotes: 1

Related Questions