0

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.

2
  • Test this: stackoverflow.com/a/66136355/10138734
    – Akina
    Jul 22, 2022 at 12:33
  • @Akina I forgot to specify I'm using MySQL 5.7. JSON_TABLE as used in the linked answer is only available in MySQL 8.
    – Baz
    Jul 22, 2022 at 13:56

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.