column creation on condition – sql

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

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'