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

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.

CodePudding user response：

You want to use conditional aggregation:

SELECT
    post_id,
    MAX(CASE meta_key WHEN 'performance_date_1' THEN meta_value END) AS date_1,
    MAX(CASE meta_key WHEN 'performance_time_1' THEN meta_value END) AS time_1,
    MAX(CASE meta_key WHEN 'performance_date_2' THEN meta_value END) AS date_2,
    MAX(CASE meta_key WHEN 'performance_time_2' THEN meta_value END) AS time_2
FROM test
GROUP BY
    post_id;

CodePudding user response：

Thanks Tim Biegeleisen for putting me on the right track. I ended up using your query as a sub query and then UNION ALL and I achieved the results I needed. The results table looks slightly different then my original question, but it works for me.

Query SQL:

SELECT
    *
FROM 
  (SELECT
      post_id,
      MAX( CASE meta_key WHEN"performance_date_1"THEN meta_value ELSE NULL END ) AS new_date_1,
      MAX( CASE meta_key WHEN"performance_time_1"THEN meta_value ELSE NULL END ) AS new_time_1
  FROM test
  GROUP BY post_id
  UNION ALL
  SELECT
      post_id,
      MAX( CASE meta_key WHEN"performance_date_2"THEN meta_value ELSE NULL END ) AS new_date_1,
       MAX( CASE meta_key WHEN"performance_time_2"THEN meta_value ELSE NULL END ) AS new_time_1
  FROM test
  GROUP BY post_id) as T1
ORDER BY post_id

Results:

CodePudding user response：

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.