I' currently in the following situation: I have a measurements
table that looks something like this:
+----+-----------+-------+---------+
| id | germplasm | trait | m_value |
+----+-----------+-------+---------+
| 1 | g1 | t1 | 1 |
| 2 | g1 | t1 | 2 |
| 3 | g1 | t2 | 3 |
+----+-----------+-------+---------+
And I need to get something of this structure to be able to easily generate a SPLOM plot (multi-dimensional scatter plot):
+-----------+----+----+
| germplasm | t1 | t2 |
+-----------+----+----+
Now I know I can use a pivot query like this:
select id, germplasm, MAX(case when trait = 't1' then value end) as 't1', MAX(case when trait = 't2' then value end) as 't2' from measurements group by germplasm
Which gives me:
+-----------+----+----+
| germplasm | t1 | t2 |
+-----------+----+----+
| g1 | 2 | 3 |
+-----------+----+----+
However, the row with id 1
gets lost in this because of the MAX
. What I'd like to get is this result:
+-----------+----+----+
| germplasm | t1 | t2 |
+-----------+----+----+
| g1 | 1 | 3 |
| g1 | 2 | 3 |
+-----------+----+----+
So basically a cross-product between the trait values for the germplasm (but still with the trait names as columns). Note that not all traits will have numeric values, some are categorical.
If I add the id
to the group by
, I receive everything in it's own row which also isn't what I want.
I tried searching for "mysql pivot cross product" or "mysql pivot duplicate values", but couldn't find anything.
Does anyone have an idea how to achieve this? Obviously my actual scenario is more complex with additional columns in the measurements
table that are used in the group by
like treatment
, replicate
etc, but I should be able to deal with those at a later date.
JSON_TABLE
as used in the linked answer is only available in MySQL 8.