MySQL: Append the columns of multiple SELECT queries as a single output line-CodePudding

This is my example data in my table:

id	date	time	label	i1	i2
1	2022-07-25	11:53:00	X	0.111	60.0
2	2022-07-25	11:53:00	Y	0.222	70.0
3	2022-07-25	11:53:00	Z	0.333	80.0
4	2022-07-25	11:53:10	Z	1.999	100.0
5	2022-07-25	11:53:10	X	1.888	200.0
6	2022-07-25	11:53:10	Y	1.777	300.0

For every time sample data is written, there are three lines (for labels "X", "Y" and "Z"). They have the same date and time but everything else is different (id is unique primary key).

What I want is an output where I append (or combine?) my related lines (=same date & time) into a single row.

This should look like this:

id	date	time	label	i1_x	i2_x	i1_y	i2_y	i1_z	i2_z
1	2022-07-25	11:53:00	XYZ	0.111	60.0	0.222	70.0	0.333	80.0
4	2022-07-25	11:53:10	XYZ	1.888	200.0	1.777	300.0	1.999	100.0

It should be noted that X,Y,Z are not always in that order (like in the example here).

How would I manage to do that?

CodePudding user response：

This should be an easy one You need to group by date and time and then use CASE statements in order to produce your output:

SELECT 
   e.`id`
  ,e.`date`
  ,e.`time`
  ,GROUP_CONCAT(DISTINCT e.`label` SEPARATOR '')  AS label
  ,SUM(CASE WHEN e.`label`='X' THEN e.`i1` ELSE 0 END) AS i1_x
  ,SUM(CASE WHEN e.`label`='X' THEN e.`i2` ELSE 0 END) AS i2_x
  ,SUM(CASE WHEN e.`label`='Y' THEN e.`i1` ELSE 0 END) AS i1_y
  ,SUM(CASE WHEN e.`label`='Y' THEN e.`i2` ELSE 0 END) AS i2_y
  ,SUM(CASE WHEN e.`label`='Z' THEN e.`i1` ELSE 0 END) AS i1_z
  ,SUM(CASE WHEN e.`label`='Z' THEN e.`i2` ELSE 0 END) AS i2_z
FROM
  `example_data` e 
GROUP BY e.`date`,
  e.`time`