MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (

meta_id bigint(20) AUTO_INCREMENT,

post_id INT,

meta_key varchar(255) NULL,

meta_value longtext NULL,

PRIMARY KEY (`meta_id`)

);

INSERT INTO test (post_id, meta_key, meta_value) VALUES (500, 'performance_date_1', '20220303');

INSERT INTO test (post_id, meta_key, meta_value) VALUES (500, 'performance_time_1', '20:00:00');

INSERT INTO test (post_id, meta_key, meta_value) VALUES (500, 'performance_date_2', '20220202');

INSERT INTO test (post_id, meta_key, meta_value) VALUES (500, 'performance_time_2', '20:00:00');

INSERT INTO test (post_id, meta_key, meta_value) VALUES (501, 'performance_date_1', '20220403');

INSERT INTO test (post_id, meta_key, meta_value) VALUES (501, 'performance_time_1', '20:00:00');

INSERT INTO test (post_id, meta_key, meta_value) VALUES (501, 'performance_date_2', '20220407');

INSERT INTO test (post_id, meta_key, meta_value) VALUES (501, 'performance_time_2', '19:00:00');

SELECT * FROM test;

meta_id	post_id	meta_key	meta_value
1	500	performance_date_1	20220303
2	500	performance_time_1	20:00:00
3	500	performance_date_2	20220202
4	500	performance_time_2	20:00:00
5	501	performance_date_1	20220403
6	501	performance_time_1	20:00:00
7	501	performance_date_2	20220407
8	501	performance_time_2	19:00:00

SELECT post_id,

MAX(CASE WHEN SUBSTRING_INDEX(meta_key, '_', 2) = 'performance_date' THEN meta_value END) new_date_1,

MAX(CASE WHEN SUBSTRING_INDEX(meta_key, '_', 2) = 'performance_time' THEN meta_value END) new_time_1

FROM test

GROUP BY post_id, SUBSTRING_INDEX(meta_key, '_', -1);