How to insert grouped data from one table to another SQL

Question

How to insert grouped data from one table to another SQL

Navigation

#1 by (0 votes)

1

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 it into another table, where the data, pee and date, are the same group all the 'time' data are grouped into 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 |          |

mysql sql

asked by anonymous 01.08.2018 / 15:07

1 answer

ASP.NET MVC - Change input to dropdown and popular with table values SQL Server Sort Properties C # [closed]

score 0 · Answer 1

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

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

Follow the 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 have only two schedules, if you have more it will bring the smallest and the largest.