MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
    Patient_AGN INT,
    MCP INT,
    P5E INT,
    CMD INT,
    KRG INT,
    EMP INT,
    CAR INT,
    SEG INT,
    CON INT,
    EMB INT,
    HP INT
);
INSERT INTO mytable (Patient_AGN, MCP, P5E, CMD, KRG, EMP, CAR, SEG, CON, EMB, HP)
VALUES 
    (1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    (2, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0),
    (3, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),
    (4, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0);

Records: 4  Duplicates: 0  Warnings: 0


CREATE VIEW myview AS
SELECT Patient_AGN, 'MCP' AS campaign FROM mytable WHERE MCP = 1
UNION ALL
SELECT Patient_AGN, 'P5E' AS campaign FROM mytable WHERE P5E = 1
UNION ALL
SELECT Patient_AGN, 'CMD' AS campaign FROM mytable WHERE CMD = 1
UNION ALL
SELECT Patient_AGN, 'KRG' AS campaign FROM mytable WHERE KRG = 1
UNION ALL
SELECT Patient_AGN, 'EMP' AS campaign FROM mytable WHERE EMP = 1
UNION ALL
SELECT Patient_AGN, 'CAR' AS campaign FROM mytable WHERE CAR = 1
UNION ALL
SELECT Patient_AGN, 'SEG' AS campaign FROM mytable WHERE SEG = 1
UNION ALL
SELECT Patient_AGN, 'CON' AS campaign FROM mytable WHERE CON = 1
UNION ALL
SELECT Patient_AGN, 'EMB' AS campaign FROM mytable WHERE EMB = 1
UNION ALL
SELECT Patient_AGN, 'HP' AS campaign FROM mytable WHERE HP = 1;
select 
       Patient_AGN
     , campaign
from myview
order by
       Patient_AGN
     , campaign


Patient_AGN
campaign


1
MCP

2
CON

2
MCP

3
CMD

4
CAR

4
SEG


select Patient_AGN
from myview
where campaign IN('CON','MCP')
group by Patient_AGN
having count(distinct campaign) = 2


Patient_AGN


2