By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T(
ID int,
region VARCHAR(5),
status VARCHAR(5),
val1 DOUBLE,
val2 DOUBLE,
val3 DOUBLE
);
INSERT INTO T VALUES (1, 'aa', 'x', 10, 11, 13);
INSERT INTO T VALUES (1, 'aa', 'y', 12, 14, 15);
INSERT INTO T VALUES (2, 'bb', 'x', null, null, null);
INSERT INTO T VALUES (2, 'bb', 'y', null, null, null);
SELECT ID,
region,
MAX(CASE WHEN status = 'y' THEN CONCAT_WS('-',val1,val2,val3) END),
MAX(CASE WHEN status = 'x' THEN CONCAT_WS('-',val1,val2,val3) END)
FROM T
GROUP BY ID,region
| ID | region | MAX(CASE WHEN status = 'y' THEN CONCAT_WS('-',val1,val2,val3) END) | MAX(CASE WHEN status = 'x' THEN CONCAT_WS('-',val1,val2,val3) END) |
|---|---|---|---|
| 1 | aa | 12-14-15 | 10-11-13 |
| 2 | bb |