How do I group texts in MySQL

Question

How do I group texts in MySQL

Navigation

#1 by (4 votes)
#2 by (0 votes)

0

I tried to find a solution to this case, and I need your help.

I have the following table:

hora    data        tarefa
10:00   02/01/19    A
10:00   03/01/19    B
11:00   02/01/19    C

Considering that today is 02/01/2019, when making the pivot table below:

SELECT      hora
        ,   CASE WHEN data = CURDATE() 
            THEN tarefa 
            ELSE NULL 
            END AS campo_1
        ,   CASE WHEN data = DATE_ADD(CURDATE(), INTERVAL 1 DAY) 
            THEN tarefa
            ELSE NULL
            END AS campo_2
FROM        tabela
WHERE       data BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 2 DAY)
GROUP BY    hora
        ,   data 
ORDER BY    hora

I get the following output:

hora    Campo_1     Campo_2
10:00   A           
10:00               B
11:00   C

However, I would like to get:

hora    Campo_1     Campo_2
10:00   A           B
11:00   C

IN SUM, I can not GROUP the time so that only one line with 10:00 appears, and jobs A and B on this same line, below their respective dates.

Thank you in advance for some solution.

mysql group-by pivot

asked by anonymous 02.01.2019 / 17:04

2 answers

Ionic 3 scroll with active class and prev / next buttons Total value of purchase by Payment with Installment (Nota Fiscal)

score 4 · Answer 1

I believe that a sub-select is responsible for the message:

select 
     hora as horaH, 
     tarefa, 
     (select tarefa from tabela where data= DATE_ADD(CURDATE(), INTERVAL 1 DAY)  and hora = horaH) 
from tabela where data = CURDATE()

This caters to the case in question, I do not know if they can have more than one task at the same time and day, but then it's up to you to adapt the code.

score 0 · Answer 2

My great friend Alan answered this question, as he has no account, I answered the question:

SELECT  hora,
    MAX(CASE WHEN data = curdate() THEN tarefa ELSE NULL END) as campo_1,
    MAX(CASE WHEN data = DATE_ADD(curdate(), INTERVAL 1 DAY) THEN tarefa ELSE NULL END) as campo_2
FROM tabela 
where data between curdate() and DATE_ADD(curdate(), INTERVAL 2 DAY)
GROUP BY hora
ORDER BY hora

Thank you all.