By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH yourTable AS (
SELECT 'ABC' AS Address, 'RF_Error' AS X1, 'PRE' AS X2 UNION ALL
SELECT 'ABC', 'RF_Error', 'PRE' UNION ALL
SELECT 'DEF', 'WIFI', 'PRE' UNION ALL
SELECT 'DEF', 'WIFI', 'PRE' UNION ALL
SELECT 'DEF', 'WIFI', 'POST' UNION ALL
SELECT 'HGI', 'RF_Error', 'PRE' UNION ALL
SELECT 'ABC', 'SYS_INFO', 'PRE' UNION ALL
SELECT 'ABC', 'SYS_INFO', 'POST'
)
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;
| Address | X1 | X2 | RF_ERROR | WIFI | SYS_INFO |
|---|---|---|---|---|---|
| ABC | RF_Error | PRE | YES | ||
| ABC | RF_Error | PRE | YES | ||
| ABC | SYS_INFO | PRE | NO | ||
| ABC | SYS_INFO | POST | NO | ||
| DEF | WIFI | PRE | NO | ||
| DEF | WIFI | PRE | NO | ||
| DEF | WIFI | POST | NO | ||
| HGI | RF_Error | PRE | YES |