Skip to content
Advertisement

SQL group two value in a same row

I have this query

    SELECT   to_char(timestamp_arr,'Dy DD/MM/YYYY') as timestamp_date 
        ,to_char(timestamp_arr,'DD/MM/YYYY') as link_date 
        ,count(transport_uid) as value 
        ,decode (statut,'DETECTED','DETECTED','NO DETECTED')  as label
FROM   V_STAT_TRACKING v 
WHERE timestamp_arr= '13/09/2019'
group by statut, timestamp_arr order by timestamp_arr, statut

And I got this result

timestamp_date  link_date   value  statut
Ven. 13/09/2019 13/09/2019  1      DETECTED
Ven. 13/09/2019 13/09/2019  21     NO DETECTED

Instead of this I want to modify my query to get this result :

timestamp_date  link_date   DETECTED NO DETECTED
Ven. 13/09/2019 13/09/2019  1         21       

They tell me about pivot function sql but I dont know how it works .

Thanks for helping

Advertisement

Answer

One option would be using pivot keyword :

select * 
  from
  ( select to_char(timestamp_arr,'Dy DD/MM/YYYY','nls_date_language=French') 
                                               as "Timestamp Date",
           to_char(timestamp_arr,'DD/MM/YYYY') as "Link Date",
           transport_uid, 
           decode (statut,'DETECTED','DETECTED','NO DETECTED') as statut 
      from v_stat_tracking )
  pivot
  (
   count(transport_uid) for statut
                        in ( 'DETECTED' as "Detected", 'NO DETECTED' as "No Detected" )
  );

Demo

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement