How to flatten a table from row to columns

Question

I use MariaDB 10.2.21 I have not seen this exact case elsewhere, hence my request for assistance.

I have a History table containing one record per change on any of the fields in a JIRA issues:

+----------+---------------+----------+-----------------+---------------------+
| IssueKey | OriginalValue | NewValue |      Field      |     ChangeDate      |
+----------+---------------+----------+-----------------+---------------------+
| HRSK-184 | (NULL)        |        2 | Risk Detection  | 2019-10-24 10:57:27 |
| HRSK-184 | (NULL)        |        2 | Risk Occurrence | 2019-10-24 10:57:27 |
| HRSK-184 | (NULL)        |        2 | Risk Severity   | 2019-10-24 10:57:27 |
| HRSK-184 | 2             |        4 | Risk Detection  | 2019-10-25 11:54:07 |
| HRSK-184 | 2             |        6 | Risk Detection  | 2019-10-25 11:54:07 |
| HRSK-184 | 2             |        3 | Risk Severity   | 2019-10-24 11:54:07 |
| HRSK-184 | 6             |        5 | Risk Detection  | 2019-10-26 09:11:01 |
+----------+---------------+----------+-----------------+---------------------+

Every record contains the old and new value and the fieldtype that has changed ('Field') and, of course, the corresponding timestamp of that change.

I want to query the point-in-time status providing me the combination of the most recent values of every of the fields 'Risk Severity, Risk Occurrence and Risk Detection'.

The result should be like this:

+----------+----------------+-------------------+------------------+----------------------+
| IssueKey | Risk Severity  |  Risk Occurrence  |  Risk Detection  |  ChangeDate          |
+----------+----------------+-------------------+------------------+----------------------+
| HRSK-184 | 3              |  2                |  5               |  2019-10-26 09:11:01 |
+----------+----------------+-------------------+------------------+----------------------+

Any ideas? I'm stuck... Thanks in advance for you effort!

Answer 1

You cold use a couple of inline queries

select 
    IssueKey,
    (
        select t1.NewValue 
        from mytable t1 
        where t1.IssueKey = t.IssueKey and t1.Field = 'Risk Severity'
        order by ChangeDate desc limit 1
    ) `Risk Severity`,
    (
        select t1.NewValue 
        from mytable t1 
        where t1.IssueKey = t.IssueKey and t1.Field = 'Risk Occurrence'
        order by ChangeDate desc limit 1
    ) `Risk Occurrence`,
    (
        select t1.NewValue 
        from mytable t1 
        where t1.IssueKey = t.IssueKey and t1.Field = 'Risk Detection'
        order by ChangeDate desc limit 1
    ) `Risk Severity`,
    max(ChangeDate) ChangeDate
from mytable t
group by IssueKey

With an index on (IssueKey, Field, ChangeDate, NewValue), this should an efficient option.

Demo on DB Fiddle:

IssueKey | Risk Severity | Risk Occurrence | Risk Severity | ChangeDate     
:------- | ------------: | --------------: | ------------: | :------------------
HRSK-184 |             3 |               2 |             5 | 2019-10-26 09:11:01