Place row data in columns

Asked

Viewed 121 times

5

SELECT datacc, horacc
FROM R070ACC WITH (NOLOCK) 
WHERE numcad = '2676' AND DATACC between ('2018-16-04') and ('2018-15-05') and oriacc = 'E'

inserir a descrição da imagem aqui

In the first column appears the day of the month repeated 4x, with different records in the second column referring to that date. (ANNEX 01)

How do I get the day to the first column and create 4 columns for the records for that same day?

Example:

DATA                  REG1                 REG2              REG3                  REG4

2018-04-16            723                  425               783                      1040

2018-04-17            733                  793               1075                     423
php sql sql-server pivot

  • Do you want to do this to display in four columns? Or organize the backend with 4 columns?

  • In addition to the DATACC column, there is another column that informs the sequence in which the lines should be read, for the same date?

2 answers

2


Here is the suggestion that uses the classic pivot to generate the 4 columns.

-- código #1 v3
with R070ACCseq as (
SELECT numcad, datacc, horacc,
       seq= row_number() over (partition by numcad, cast(datacc as date) 
                               order by (SELECT 0))
  from R070ACC 
  where datacc between '2018-04-16' and '2018-05-15'
        and numcad = '2676' 
        and oriacc = 'E'
)
SELECT numcad, datacc, 
       max(case when seq = 1 then horacc end) as REG1,
       max(case when seq = 2 then horacc end) as REG2,
       max(case when seq = 3 then horacc end) as REG3,
       max(case when seq = 4 then horacc end) as REG4
  from R070ACCseq
  group by numcad, datacc;

A table is a set of data without order. If it is necessary that for the same date the columns REG1 to REG4 list the values in the order in which they appear in the example, then it is necessary that there is a column indicating in which order the rows should be manipulated.

(...) has the shape of [hh:mm]?

-- código #1 v4
set dateformat ydm;

with R070ACCseq as (
SELECT numcad, cast(datacc as date) as datacc, 
       convert(char(5), dateadd(minute, horacc, 0), 108) as horacc,
       seq= row_number() over (partition by numcad, cast(datacc as date) 
                               order by horacc)
  from R070ACC 
  where datacc between '2018-16-05' and '2018-15-06'
        and numcad = '2676' 
)
SELECT numcad, datacc, 
       max(case when seq = 1 then horacc end) as REG1,
       max(case when seq = 2 then horacc end) as REG2,
       max(case when seq = 3 then horacc end) as REG3,
       max(case when seq = 4 then horacc end) as REG4
  from R070ACCseq
  group by numcad, datacc;

  • @Jeffersonlucena Added the "code #1 v4" with the treatment of time format.

  • Top José Diz... thank you!

0

with R070ACCseq as (
SELECT numcad, datacc, horacc,
       seq= row_number() over (partition by numcad, cast(datacc as date) 
                               order by datacc, horacc)
  from R070ACC   
  where datacc between ('2018-16-05') and ('2018-15-06')
        and numcad = '2676' 
)
SELECT numcad, datacc, 
       max(case when seq = 1 then CONVERT(VARCHAR, DATEADD(minute,horacc,0),108) end) as REG1,
       max(case when seq = 2 then CONVERT(VARCHAR, DATEADD(minute,horacc,0),108) end) as REG2,
       max(case when seq = 3 then CONVERT(VARCHAR, DATEADD(minute,horacc,0),108) end) as REG3,
       max(case when seq = 4 then CONVERT(VARCHAR, DATEADD(minute,horacc,0),108) end) as REG4 
  from R070ACCseq 
  group by numcad, datacc

inserir a descrição da imagem aqui

José Diz, it worked... I ordered by the fields datacc, horacc and with that remained in the sequence the point records, but when making the conversion from minutes to time appears the seconds, it has to stay in the format [hh:mm]?

Browser other questions tagged php sql sql-server pivot

You are not signed in. Login or sign up in order to post.