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!

score 2 · Accepted Answer · answered Nov 15 '19 at 08:48

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

This is indeed what I am looking for. I will tune&twek a little more. Thank you @GMB! — Szjoin, Nov 15 '19 at 12:11

Barbaros Özhan · Answer 2 · 2019-11-15T12:31:09.010

MariaDB 10.2 has introduced some Window Functions for analytical queries.

One of them is RANK() OVER (PARTITION BY ...ORDER BY...) function.

Firstly, you can apply it, and then pivot through Conditional Aggregation :

SELECT IssueKey,
       MAX(CASE WHEN Field = 'Risk Severity'   THEN NewValue END ) AS RiskSeverity,
       MAX(CASE WHEN Field = 'Risk Occurrence' THEN NewValue END ) AS RiskOccurrence,
       MAX(CASE WHEN Field = 'Risk Detection'  THEN NewValue END ) AS RiskDetection,
       MAX(ChangeDate) AS ChangeDate
  FROM
  (
   SELECT RANK() OVER (PARTITION BY IssueKey, Field ORDER BY ChangeDate Desc) rnk,
          t.*
     FROM mytable t 
  ) t
  WHERE rnk = 1
  GROUP BY IssueKey;

IssueKey | RiskSeverity  | RiskOccurrence  | RiskDetection  | ChangeDate     
-------- + --------------+-----------------+----------------+--------------------
HRSK-184 |             3 |               2 |              5 | 2019-10-26 09:11:01

Demo

How to flatten a table from row to columns

2 Answers2