Table:
col1 col2 col3 236 1234 lion 236 1234 235 1023 235 1234 234 1234 232 1234 232 1234 tiger 231 1234 231 1234 cat
Aim to create a col4 and copy value from col3 to col4 if (with partition by on col2):
Wherever “cat” is present in col3, then check if col1+5 exists, if yes then value of col3 from “cat” row goes to that col4(whose col1 value is cat’s “col1+5”)
output:
col1 col2 col3 col4 236 1234 lion 236 1234 cat 235 1023 235 1234 234 1234 232 1234 232 1234 tiger 231 1234 231 1234 cat
Advertisement
Answer
If I understand correctly, you can use left join:
select t.*,
(case when t.col3 is null then t5.col3 end) as col4
from t left join
t t5
on t5.col1 = t.col1 + 5 and t5.col2 = t.col2 and t5.col3 = 'cat'