Place data from a row in columns

Question

Place data from a row in columns

Navigation

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

5

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

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 stay in 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

asked by anonymous 16.06.2018 / 17:27

2 answers

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

Jose says, it worked ... I ordered the fields datacc, horacc and with that it was in sequence the records of point, but when doing the conversion from minutes to hour the seconds appear, it has to be in the format [hh: mm ]?

18.06.2018 / 13:48

How to find out the path followed by a function in js Change view / component but not URL

score 2 · Accepted Answer

Here is a 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 an unordered data set. If it is necessary that for the same date the columns REG1 to REG4 list the values in the order they appear in the example, then it is necessary that there is a column that tells what order the rows are to be manipulated.

-- 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;