Simple Pivot (without aggregation)

[brunomruiz]

Goodnight.

I have a simple doubt, but that is taking my sleep. How do I do a pivot without aggregation? I need to "transpose" these schedules ... [url=https://imgbb.com/]

Select all

select --a.PIS, 
       --to_char(cast(a.DATAHORA as date), 'dd-mon-rrrr') dia, 
       to_char(cast(a.DATAHORA as date), 'hh24:mm') hora 
       --count(a.PIS) contador 
  from coleta a 
 where 1 = 1 
      --and    to_char(cast(a.DATAHORA as date),'dd-mon-rrrr') = pdData 
   and a.DATAHORA >= '04-jun-2018' 
   and a.PIS = '20474191251' 
   and a.DATAHORA <= '05-jun-2018' 
   and to_char(cast(a.DATAHORA as date), 'd') not in ('7', '8') --não busca o sábado e domingo 
 --group by a.PIS, to_char(cast(a.DATAHORA as date), 'dd-mon-rrrr'),to_char(cast(a.DATAHORA as date), 'hh24:mm')  
--having count(a.PIS) <> '4' removido devido ao if buscando o contador 
 --order by 1, 2 
;

[DanielNN]

I did not understand exactly your need.
Do you want to spend every hour: minute for columns?
Or would it be every hour the columns and in them would have the minutes of it belonging?

How would it be desired?

[brunomruiz]

I did not understand exactly your need.
Do you want to spend every hour: minute for columns?
Or would it be every hour the columns and in them would have the minutes of it belonging?

How would you be desired?

Hello. In this select I commented on the pis, but it is an exit idea is:
pis, schedule 1, schedule 2, schedule 3, schedule 4.

Transpose hour and minute for columns and No lines.

[DanielNN]

What I do not understand is the need to transpose HH: MI to column.
This way there are 1440 columns possible to store it. And doing this dynamically complicated.

looks:
http://en.glufke.net/oracle/viewtopic.p ... vot#p41564
http://en.glufke.net/oracle/viewtopic.p ... vot#p41600

[brunomruiz]

What I do not understand is the need to transpose HH: MI to column.
This way there are 1440 columns possible to store it. And doing this dynamically complicated.

looks:


http://en.glufke.net/oracle/viewtopic.p ... vot#p41600 / quote]
Hi Daniel, I was traveling and on the way I stopped in the Hospital, good, come on. I do not intend to "pivot" 1440 columns, because I'm going to break by pis and date, I would like this:
Pis Date Time 1 hour 2 hour 3 hour 4
123456 07/07/18 07:40 12 : 01 13:13 17:00

I'm reading your articles. If you have a light I already return. It cost.

[DanielNN]

So is limited to 4 schedules? Or can you have more than 4 for each date / pis?

[brunomruiz]

Is then limited to 4 schedules? Or can you have more than 4 for each date / pis?

is limited to 4 times. What is different from 4 I trained otherwise before, this is the clean final product of the analysis.

[DanielNN]

As I do not know which version you are using but I did "manual" without using the pivot function (introduced in version 11).
I used an analytical function feature to tell me what the number / position of that time in that PIS / day.
I believe that it meets you.

Select all

with COLETA AS ( 
select 'PIS1' PIS , '01/01/2018' DIA , '01:02' HORA FROM DUAL UNION ALL 
select 'PIS1' PIS , '01/01/2018' DIA , '09:02' HORA FROM DUAL UNION ALL 
select 'PIS1' PIS , '01/01/2018' DIA , '09:20' HORA FROM DUAL UNION ALL 
select 'PIS1' PIS , '01/01/2018' DIA , '13:34' HORA FROM DUAL UNION ALL 
select 'PIS2' PIS , '01/01/2018' DIA , '03:02' HORA FROM DUAL UNION ALL 
select 'PIS2' PIS , '01/01/2018' DIA , '04:54' HORA FROM DUAL UNION ALL 
select 'PIS2' PIS , '01/01/2018' DIA , '22:32' HORA FROM DUAL UNION ALL 
select 'PIS2' PIS , '03/01/2018' DIA , '01:02' HORA FROM DUAL UNION ALL 
select 'PIS2' PIS , '03/01/2018' DIA , '09:02' HORA FROM DUAL UNION ALL 
select 'PIS2' PIS , '03/01/2018' DIA , '09:20' HORA FROM DUAL UNION ALL 
select 'PIS2' PIS , '03/01/2018' DIA , '13:34' HORA FROM DUAL 
) 
, DADOS AS ( 
SELECT C.PIS 
     , C.DIA 
     , C.HORA 
     , ROW_NUMBER() OVER (PARTITION BY C.PIS, C.DIA ORDER BY C.HORA) LINHA 
  FROM COLETA C 
  )   
SELECT D.PIS 
     , D.DIA 
     , MAX(CASE WHEN LINHA = 1 THEN D.HORA END) HORA1 
     , MAX(CASE WHEN LINHA = 2 THEN D.HORA END) HORA2 
     , MAX(CASE WHEN LINHA = 3 THEN D.HORA END) HORA3 
     , MAX(CASE WHEN LINHA = 4 THEN D.HORA END) HORA4 
  FROM DADOS D 
GROUP BY D.PIS 
       , D.DIA 
ORDER BY 1,2