Order By date for multiple date rows

Question

I am trying to write an sql query to combine a rows so the result will look something like this:

post_id	date_1	time_1	date_2	time_2
500	20220303	20:00:00
500			20220202	20:00:00

Here is what Ive tried so far:

SELECT 
    CASE meta_key WHEN"performance_date_1"THEN meta_value ELSE NULL END  AS date_1,
    CASE meta_key WHEN"performance_date_2"THEN meta_value ELSE NULL END  AS date_2
FROM test;

Schema SQL:

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');

SQL Fiddle https://www.db-fiddle.com/f/9Zxq4HJpuAiSPvabQzL4ux/1

Any help would be appreciated.

Answer 1

For this sample data you can simplify your code to:

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);

See the demo.