CODEWITHSUNDEEP
sqldatabaseoracle-databasegroup-bypivot
DEVLOGIN
DEVLOGIN

Reputation: 87

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

Upvotes: 2

Views: 81

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

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

Upvotes: 2

GMB
GMB

Reputation: 222722

You can use conditional aggregation, as commented by jarlh

select 
    timestamp_date, 
    link_date,
    sum(case when statut = 'DETECTED' then value else 0 end) detected,
    sum(case when statut = 'NOT DETECTED' then value else 0 end) not_detected
from v_stat_tracking 
group by 
    timestamp_date, 
    link_date

Upvotes: 2

Related Questions