Flagging column based on condition using SQL

I have a table like this ,

Address  X1         X2    
ABC      RF_Error   PRE
ABC      RF_Error   PRE
DEF      WIFI       PRE
DEF      WIFI       PRE
DEF      WIFI       POST
HGI      RF_Error   PRE
ABC      SYS_INFO   PRE
ABC      SYS_INFO   POST

I want to check for Address with respect to X1 , POST is not occurred in X2 then I want to flag with the X1 value as Yes or No.

My output should look like,

Address  X1         X2     RF_ERROR   WIFI   SYS_INFO
ABC      RF_Error   PRE       YES
ABC      RF_Error   PRE       YES
DEF      WIFI       PRE                NO
DEF      WIFI       PRE                NO
DEF      WIFI       POST               NO
HGI      RF_Error   PRE       YES
ABC      SYS_INFO   PRE                          NO
ABC      SYS_INFO   POST                         NO

Answer

One simple option would be to use aggregation to generate the flags you want, and then join back to your original table:

SELECT
    t1.Address,
    t1.X1,
    t1.X2,
    t2.RF_ERROR,
    t2.WIFI,
    t2.SYS_INFO
FROM yourTable t1
INNER JOIN
(
    SELECT
        Address,
        X1,
        IF(COUNT(CASE WHEN X2 = 'POST' THEN 1 END) = 0, 'YES', '') AS RF_ERROR,
        IF(COUNT(CASE WHEN X1 = 'WIFI' THEN 1 END) > 0, 'NO', '') AS WIFI,
        IF(COUNT(CASE WHEN X1 = 'SYS_INFO' THEN 1 END) > 0, 'NO', '') AS SYS_INFO
    FROM yourTable
    GROUP BY Address, X1
) t2
    ON t1.Address = t2.Address AND t1.X1 = t2.X1
ORDER BY
    t1.Address;

Demo