I want to create a new column where If my Rom Indicator is 'Y', then pick the Account ID value and swap it for all the IDs as shown below

I tried using Case statments like this CASE WHEN PRIM_IND = 'Y' THEN ACT_ID ELSE ACT_ID END

CodePudding user response：

select  t.*
       ,max(case when PRI_IND = 'Y' then ACT_ID end) over(partition by ID) as ACT_ID_NEW
from    t  

Fiddle

CodePudding user response：

Not sure if this is what you wanted, but the output matches so I guess so. It's just a basic self INNER JOIN where you only want to join on the records with pri_ind = 'Y'

SELECT a.id,
       a.act_id,
       a.pri_ind,
       b.act_id AS act_id_new
FROM   tab1 a
       INNER JOIN tab1 b
               ON a.id = b.id
                  AND b.pri_ind = 'Y'