How to insert grouped data from one table to another SQL

Question

I have a 'point' table, with columns (id, Pis, date, time), which record the input and output of a product ('Pis'). I wanted to group in another table, where the data, Pis and date, are equal group all the data 'time' are grouped in a single column. Ex Table 1

+-------+--------------+-------------+----------+
| id    | pis          | data        | hora     |
+-------+--------------+-------------+----------+
| 1     | 12113879222  | 2018-02-02  | 07:21:00 |
| 2     | 12113879222  | 2018-02-02  | 11:59:00 |
| 3     | 21056646219  | 2018-02-02  | 07:32:00 | 
| 4     | 21056646219  | 2017-05-17  | 12:01:00 |

For table 2

+-------+--------------+-------------+----------+----------+
| id    | pis          | data        | hora1    | hora2    |
+-------+--------------+-------------+----------+----------+
| 1     | 12113879222  | 2018-02-02  | 07:21:00 | 11:59:00 |
| 3     | 21056646219  | 2018-02-02  | 07:32:00 |          |
| 4     | 21056646219  | 2017-05-17  | 12:01:00 |          |

Answer 1

What you are looking for is a GROUP BY associated with SUBSTRING_INDEX and GROUP_CONCAT functions. In Mysql, you will get something like this:

SELECT id, pis, data,
       SUBSTRING_INDEX(GROUP_CONCAT(hora ORDER BY hora ASC), ",", 1) AS hora1,
       SUBSTRING_INDEX(GROUP_CONCAT(hora ORDER BY hora ASC), ",", 2) AS hora2
FROM tabela
GROUP BY pis, data;

If you want more than two columns for "time", just define in the code, remembering to change the argument regarding the number of times that the delimiter should be searched before the extraction by the SUBSTRING_INDEX function for each new column. A general example:

SELECT id, pis, data,
       SUBSTRING_INDEX(GROUP_CONCAT(hora ORDER BY hora ASC), ",", 1) AS hora1,
       SUBSTRING_INDEX(GROUP_CONCAT(hora ORDER BY hora ASC), ",", 2) AS hora2,
       SUBSTRING_INDEX(GROUP_CONCAT(hora ORDER BY hora ASC), ",", 3) AS hora3,
       SUBSTRING_INDEX(GROUP_CONCAT(hora ORDER BY hora ASC), ",", 4) AS hora4,
       ...,
       SUBSTRING_INDEX(GROUP_CONCAT(hora ORDER BY hora ASC), ",", N) AS horaN
FROM tabela
GROUP BY pis, data;

---

Just a warning: GROUP_CONCAT has a maximum limit of 1024 characters, which can be increased using the command:

SET SESSION group_concat_max_len = val;

for session-only change or

SET GLOBAL group_concat_max_len = val;

for global change; "val" is the (integer) value you choose.

Answer 2

I thought of a method here very quiet, you will group and select the MIN of the time field and the MAX, make a case when the MIN and the MAX are equal bring null.

Then just make a recursive to bring the id hitting with the highest date and insert into the new table.

Follow code in SQL Server 2012

create table teste
(
id numeric, 
pis numeric, 
data date, 
hora datetime
)

create table teste_pronto
(
id numeric, 
pis numeric, 
data date, 
hora1 datetime,
hora2 datetime
)

insert teste values (1,1211,'2018-02-02','07:21');
insert teste values (2,1211,'2018-02-02','11:59');
insert teste values (3,2105,'2018-02-02','07:32');
insert teste values (4,2105,'2018-05-17','07:21')

With TBL 
as
(
Select pis,data,min(hora)hora1, 
Case 
When min(hora) = max(hora)
Then null
Else max(hora)
End Hora2
from teste
group by pis,data
)
insert teste_pronto
Select T2.id,T1.* from TBL T1 inner join Teste T2
on T1.pis = T2.pis and T2.data = T2.data and T1.hora1 = T2.hora


Select * from teste_pronto

Note: it will only work if you only have two hours, if you have more it will bring the smallest and the largest.