CODEWITHSUNDEEP
sqlsql-serverdatabase
user2302158
user2302158

Reputation: 463

How to duplicate the values based on id

I need to select the value based on the condition form the table for eg:

Id   name   value  status  priority
 1   ram    23     p       high
 1   kev    23     p       Medi
 1   Mac    23     h       high
 2   Dot    25     f       low
 2   Dot    25     h       Avg
 3   Pat    25     f       low
 3   vin    25     u       low

If you see when status = h the priority column should be changed to high based on the id column during the select

the result must be below table 

 Id   name   value  status  priority
 1    ram     23       p       high
 1    kev     23       p       high
 1    Mac     23       h       high
 2    Dot     25       f       high
 2    Dot     25       h       high
 3    Pat     25       f       low
 3    vin     25       u       low

help me

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

If I understand correctly, you want window functions with some conditional logic:

select t.id, t.name, t.value, t.status,
       (case when sum(case when status = 'h' then 1 else 0 end) over (partition by id) > 0
             then 'high'
             else priority
        end) as priority
from t;

Upvotes: 3

Related Questions