Turn specific rows into columns

Question

I have a table of activity histories and would like to turn some rows (of some specific status) into columns:

Current Table:

The statuses I wish to turn into columns are these:

mod_analise_status_id = 10 (FORMALIZADO)

mod_analise_status_id = 9 OR mod_analise_status_id = 12 (CONFERIDO)

That is, create a column FORMALIZED and another column CONFERRED where the column value will be displayed datetime. I’d stay that way down:

Desired table:

In my example the CHECKED column did not return the right values. My SQL code is this:

SELECT
h.solicitation_id as 'SOLICITATION',
case 
    when (h.mod_analise_status_id = 10)   then h.datetime
    else h.datetime
end
as 'FORMALIZADO',
case 
    when (h2.mod_analise_status_id =9 AND h2.mod_analise_status_id =12) then h2.datetime
    else h2.datetime
end
as 'CONFERIDO'
FROM
    `negocios`.`mod_analise_historic` h
    INNER JOIN `negocios`.`mod_analise_historic` h2
    ON h.solicitation_id = h2.solicitation_id AND MONTH (h.datetime) = 06
WHERE
    MONTH (h.datetime) = 06
    AND YEAR (h.datetime) = 2021

Table: http://sqlfiddle.com/#! 9/8f4f3b

Answer 1

In the situation where you have a history table, basically, we will have a table with 3 fields: ID, Datarecord, Observation; These fields will answer the questions: With which record happened? When? What? Who?.
The field ID: related to another table, in this example, we will consider the solicitation_id which must be related to a business table, probably called solicitation.
The field DataHoraRegistro: Informs when an event has occurred, we will admit the field called datetime in his case.
The field Observação, status_id, important_information: It will contain relevant information from that record.
Note an example of the relationship between tables that contains a history table:

Example available in: SQL Fiddle

Mysql 5.6 Schema Setup:

create table mod_analise_historic
(
    id bigint unsigned auto_increment,
    solicitation_id int null,
    datetime datetime null,
    mod_analise_status_id int null,
    constraint id
        unique (id)
);

insert into mod_analise_historic (id, solicitation_id, datetime, mod_analise_status_id) values (1, 25279, '2021-06-01 12:00:00', 10);
insert into mod_analise_historic (id, solicitation_id, datetime, mod_analise_status_id) values (2, 25246, '2021-06-01 06:46:59', 10);
insert into mod_analise_historic (id, solicitation_id, datetime, mod_analise_status_id) values (3, 24842, '2021-06-01 14:00:00', 10);
insert into mod_analise_historic (id, solicitation_id, datetime, mod_analise_status_id) values (4, 25246, '2021-06-01 08:47:46', 9);
insert into mod_analise_historic (id, solicitation_id, datetime, mod_analise_status_id) values (5, 25246, '2021-05-20 07:00:00', 1);
insert into mod_analise_historic (id, solicitation_id, datetime, mod_analise_status_id) values (6, 25246, '2021-06-01 08:30:00', 12);

Consultation 1:

Select * from mod_analise_historic

Upshot:

id	solicitation_id	datetime	mod_parse_status_id
1	25279	2021-06-01T12:00:00Z	10
2	25246	2021-06-01T06:46:59Z	10
3	24842	2021-06-01T14:00:00Z	10
4	25246	2021-06-01T08:47:46Z	9
5	25246	2021-05-20T07:00:00Z	1
6	25246	2021-06-01T08:30:00Z	12

Query 2 - Displaying 'Formalized' and shorter date for 'Conferred':

SELECT
h.solicitation_id as 'SOLICITATION',
case
    when (h.mod_analise_status_id = 10)   then h.datetime
end
as 'FORMALIZADO',
case
    when (h2.mod_analise_status_id =9 or h2.mod_analise_status_id =12) then h2.datetime
end
as 'CONFERIDO'
FROM
    `mod_analise_historic` h
    INNER JOIN `mod_analise_historic` h2
            ON h.solicitation_id = h2.solicitation_id
           and (h2.mod_analise_status_id =9 or h2.mod_analise_status_id =12)
           and h2.datetime = (Select min(datetime)
                                from mod_analise_historic h3
                               where h3.solicitation_id = h2.solicitation_id
                                 and YEAR(h3.datetime) = YEAR(h2.datetime)
                                 and (h3.mod_analise_status_id =9 or h3.mod_analise_status_id =12)
               )
WHERE
    MONTH (h.datetime) = 06
    AND YEAR (h.datetime) = 2021
    AND H.mod_analise_status_id = 10

Results:

SOLICITATION	FORMALIZED	CONFERRED
25246	2021-06-01T06:46:59Z	2021-06-01T08:30:00Z

Query 3: Set of records that are 'FORMALIZED' status'

SELECT h.solicitation_id as 'SOLICITATION',
case
    when (h.mod_analise_status_id = 10)   then h.datetime
end
as 'FORMALIZADO'
FROM
    `mod_analise_historic` h
WHERE
    MONTH(h.datetime) = 06
    AND YEAR (h.datetime) = 2021

Results:

SOLICITATION	FORMALIZED
25279	2021-06-01T12:00:00Z
25246	2021-06-01T06:46:59Z
24842	2021-06-01T14:00:00Z
25246	(null)
25246	(null)

Query 4: 'Conferred' with date null when the status does not mean 'Conferred'.

SELECT h2.solicitation_id as 'SOLICITATION',
case
    when (h2.mod_analise_status_id =9 or h2.mod_analise_status_id =12) then h2.datetime
end
as 'CONFERIDO'
from `mod_analise_historic` h2

Results:

SOLICITATION	CONFERRED
25279	(null)
25246	(null)
24842	(null)
25246	2021-06-01T08:47:46Z
25246	(null)
25246	2021-06-01T08:30:00Z

Query 5:

In this way, we obtained the data set that are in the 'Checked' situation and display the date of the conference. Now, since we are only interested in those who are conferred, it should be limited in the clause where, being like this:

SELECT h2.solicitation_id as 'SOLICITATION',
case
    when (h2.mod_analise_status_id =9 or h2.mod_analise_status_id =12) then h2.datetime
end
as 'CONFERIDO'
from `mod_analise_historic` h2
where (h2.mod_analise_status_id =9 or h2.mod_analise_status_id =12)

Note that there are two records for the same solicitation_id, and there may be several others in the case.

Results:

SOLICITATION	CONFERRED
25246	2021-06-01T08:47:46Z
25246	2021-06-01T08:30:00Z

Query 6:

Now we have only those who satisfy the expected condition for the second set. But we can observe that, because it is a historical, common situation in many databases, there may be repetitions, or as in the situation in question, more than one status meaning 'conferred', so now just limit the result with a sub-allowance that will bring the earliest date, or longer date as expected result.

Lowest date using function MIN( ):

  and h2.datetime = (Select MIN(datetime)
                                from mod_analise_historic h3
                               where h3.solicitation_id = h2.solicitation_id
                                 and YEAR(h3.datetime) = YEAR(h2.datetime)
                                 and (h3.mod_analise_status_id =9 or h3.mod_analise_status_id =12)
                     )

Higher date using function MAX( ):

  and h2.datetime = (Select MAX(datetime)
                                from mod_analise_historic h3
                               where h3.solicitation_id = h2.solicitation_id
                                 and YEAR(h3.datetime) = YEAR(h2.datetime)
                                 and (h3.mod_analise_status_id =9 or h3.mod_analise_status_id =12)
                     )

Minor elapsed time for conference:

SELECT h2.solicitation_id as 'SOLICITATION',
case
    when (h2.mod_analise_status_id =9 or h2.mod_analise_status_id =12) then h2.datetime
end
as 'CONFERIDO'
from `mod_analise_historic` h2
where (h2.mod_analise_status_id =9 or h2.mod_analise_status_id =12)
  and h2.datetime = (Select MIN(datetime)
                                from mod_analise_historic h3
                               where h3.solicitation_id = h2.solicitation_id
                                 and YEAR(h3.datetime) = YEAR(h2.datetime)
                                 and (h3.mod_analise_status_id =9 or h3.mod_analise_status_id =12)
                     )

Results:

SOLICITATION	CONFERRED
25246	2021-06-01T08:30:00Z

Query 7:

Greater elapsed time for conference:

SELECT h2.solicitation_id as 'SOLICITATION',
case
    when (h2.mod_analise_status_id =9 or h2.mod_analise_status_id =12) then h2.datetime
end
as 'CONFERIDO'
from `mod_analise_historic` h2
where (h2.mod_analise_status_id =9 or h2.mod_analise_status_id =12)
  and h2.datetime = (Select MAX(datetime)
                                from mod_analise_historic h3
                               where h3.solicitation_id = h2.solicitation_id
                                 and YEAR(h3.datetime) = YEAR(h2.datetime)
                                 and (h3.mod_analise_status_id =9 or h3.mod_analise_status_id =12)
                     )
                  

Results:

SOLICITATION	CONFERRED
25246	2021-06-01T08:47:46Z

Another example using sub-consumption with min() or max(): Subconsultation using Max()
See more about Joins.