CODEWITHSUNDEEP
sqlsql-server
konichiwa
konichiwa

Reputation: 551

How to set value based on value existence in SQL Server?

I have the following T-SQL code:

select 
    id,
    (case
        when n in(Bla1', 'Bla2') then 1
        when n = 'Bla3' then 99
        else 0
     end) as c
from 
    hello

Running this code outputs this result:

|  id    | c  |
+--------+----+
| 577140 | 0  |
| 577140 | 1  |
| 577140 | 0  |
| 577140 | 0  |
| 577140 | 99 |
| 577141 | 0  |
| 577141 | 0  |
| 577141 | 0  |
| 577142 | 0  |
| 577142 | 0  |
| 577142 | 1  |

How can I modify the code to get the following output?

|  id    | c  |
+--------+----+
| 577140 | 99 |
| 577141 | 0  |
| 577142 | 1  |

Rule

For each id: If 99 exists, then c becomes 99. If not, either 1 or 0, depending if any 1 exists.

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use aggregation:

select id,
       max(case when n in ('Bla1', 'Bla2') then 1
                when n = 'Bla3' then 99
                else 0
           end) as c
from hello
group by id;

Upvotes: 1

Related Questions