By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `table` (
date VARCHAR(10),
sid INT,
comp VARCHAR(2),
disc INT
);
INSERT INTO `table`
VALUES
('23 june', 1, 'az', 20),
('23 june', 1, 'ph', 22),
('23 june', 1, 'mg', 10),
('23 june', 2, 'mg', 8),
('23 june', 3, 'ph', 15),
('23 june', 3, 'az', 11);
WITH cte AS (
SELECT t1.*, t2.comp as comp1, t2.disc as disc1, SUM(1) OVER(PARTITION BY date,sid,comp) AS cnt
FROM `table` t1
LEFT JOIN `table` t2 ON t1.date = t2.date AND t1.sid = t2.sid AND t1.comp <> t2.comp
)
SELECT
date, sid, comp, disc,
CASE WHEN comp1 <> 'mg' THEN NULL ELSE comp1 END AS comp1,
CASE WHEN comp1 <> 'mg' THEN NULL ELSE disc1 END AS disc1
FROM cte
WHERE
(CASE WHEN comp <> 'mg' OR comp1 IS NULL THEN cnt END) = 1
OR
(CASE WHEN comp <> 'mg' AND comp1 = 'mg' THEN cnt END) >= 2
| date | sid | comp | disc | comp1 | disc1 |
|---|---|---|---|---|---|
| 23 june | 1 | az | 20 | mg | 10 |
| 23 june | 1 | ph | 22 | mg | 10 |
| 23 june | 2 | mg | 8 | ||
| 23 june | 3 | az | 11 | ||
| 23 june | 3 | ph | 15 |
TRUNCATE `table`;
INSERT INTO `table`
VALUES
('23 june', 1, 'ph', 22),
('23 june', 1, 'mg', 10),
('23 june', 2, 'mg', 8),
('23 june', 3, 'ph', 15),
('23 june', 3, 'az', 11);
WITH cte AS (
SELECT t1.*, t2.comp as comp1, t2.disc as disc1, SUM(1) OVER(PARTITION BY date,sid,comp) AS cnt
FROM `table` t1
LEFT JOIN `table` t2 ON t1.date = t2.date AND t1.sid = t2.sid AND t1.comp <> t2.comp
)
SELECT
date, sid, comp, disc,
CASE WHEN comp1 <> 'mg' THEN NULL ELSE comp1 END AS comp1,
CASE WHEN comp1 <> 'mg' THEN NULL ELSE disc1 END AS disc1
FROM cte
WHERE
(CASE WHEN comp <> 'mg' OR comp1 IS NULL THEN cnt END) = 1
OR
(CASE WHEN comp <> 'mg' AND comp1 = 'mg' THEN cnt END) >= 2
| date | sid | comp | disc | comp1 | disc1 |
|---|---|---|---|---|---|
| 23 june | 1 | ph | 22 | mg | 10 |
| 23 june | 2 | mg | 8 | ||
| 23 june | 3 | az | 11 | ||
| 23 june | 3 | ph | 15 |