CODEWITHSUNDEEP
sqloracle-database
mdkamrul
mdkamrul

Reputation: 274

Merge 2 rows into one with 2 columns with pivot

Hello I have tried to merge two rows into one column with In and out time for a agency. Here is my query

   select
   agency_name,       
   "IN",
   out
   from   (
       select 
        eofficeuat.entrylog_vehicle.agent_id,

        eofficeuat.cnf_agents.agent_name,
        eofficeuat.gatepass.agency_name,
        eofficeuat.gatepass.agency_id,
        TO_CHAR(eofficeuat.entrylog_vehicle.scantime, 'dd-mm-yyyy HH12:MI:SS PM') as Time,            
        eofficeuat.entrylog_vehicle.action as action,
        eofficeuat.gatelist.shortname as gate,
        eofficeuat.entrylog_vehicle.passnumber,
        eofficeuat.entrylog_vehicle.cardnumber,
        eofficeuat.gatepass.vehicletype,
        eofficeuat.gatepass.ISSUEDATETIME,
        row_number() over (
        partition by agency_name, trunc(to_date(TO_CHAR(eofficeuat.entrylog_vehicle.scantime, 'dd-mm-yyyy HH12:MI:SS PM'), 'dd-mm-yyyy hh:mi:ss pm')), action order by eofficeuat.entrylog_vehicle.scantime) rn
    FROM
        eofficeuat.entrylog_vehicle
        INNER JOIN eofficeuat.cnf_agents ON eofficeuat.entrylog_vehicle.agent_id = eofficeuat.cnf_agents.agent_id
        INNER JOIN eofficeuat.gatelist ON eofficeuat.entrylog_vehicle.gate_id = eofficeuat.gatelist.id
        INNER JOIN eofficeuat.gatepass ON eofficeuat.entrylog_vehicle.passnumber = eofficeuat.gatepass.id

    WHERE 

        eofficeuat.entrylog_vehicle.passnumber = '10000920616'
        and eofficeuat.entrylog_vehicle.scantime between TO_DATE ('03/10/2019', 'dd/mm/yyyy') and TO_DATE ('04/10/2019', 'dd/mm/yyyy') 


    )
  pivot (max(time) for action in ('IN' as "IN", 'OUT' as "OUT"))
  order by rn;

and here is Fiddle http://sqlfiddle.com/#!4/d465a/1 I wanted to merge two rows into one which have in and out for 03-10-2019 date and similarly for 04-10-2019 date. thats why I used analytics function . but this not merging into one rows. so there showing 4 rows but I wanted 2 rows to show. can you please help me for this?

Upvotes: 0

Views: 78

Answers (3)

MT0
MT0

Reputation: 168806

You can simplify it a lot and use TRUNC instead of ROW_NUMBER:

Oracle Setup:

Don't store dates as strings; use appropriate data types, so in this case, DATE:

create table kvtest ( agency_name, action, TIME ) AS
SELECT 'ABC LIMITED', 'IN',  DATE '2019-10-03' + INTERVAL '15:52:26' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'ABC LIMITED', 'OUT', DATE '2019-10-03' + INTERVAL '18:17:48' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'ABC LIMITED', 'IN',  DATE '2019-10-04' + INTERVAL '15:52:26' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'ABC LIMITED', 'OUT', DATE '2019-10-04' + INTERVAL '18:17:48' HOUR TO SECOND FROM DUAL;

Query:

SELECT agency_name,
       "IN",
       OUT
FROM   (
  SELECT T.*,
         TRUNC( TIME ) AS day
  FROM   kvtest t
)
PIVOT ( MIN( time ) FOR action IN ( 'IN' AS "IN", 'OUT' AS "OUT" ) )
ORDER BY day;

Output:

| AGENCY_NAME |                   IN |                  OUT |
|-------------|----------------------|----------------------|
| ABC LIMITED | 2019-10-03T15:52:26Z | 2019-10-03T18:17:48Z |
| ABC LIMITED | 2019-10-04T15:52:26Z | 2019-10-04T18:17:48Z |

SQL Fiddle

Update:

from comment: agency will have many in and out daily so I used the case to get first in and last out time

Oracle Setup:

create table kvtest ( agency_name, action, TIME ) AS
SELECT 'ABC LIMITED', 'IN',  DATE '2019-10-03' + INTERVAL '15:52:26' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'ABC LIMITED', 'OUT', DATE '2019-10-03' + INTERVAL '18:17:48' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'ABC LIMITED', 'IN',  DATE '2019-10-03' + INTERVAL '18:52:26' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'ABC LIMITED', 'OUT', DATE '2019-10-03' + INTERVAL '19:17:48' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'ABC LIMITED', 'IN',  DATE '2019-10-04' + INTERVAL '15:52:26' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'ABC LIMITED', 'OUT', DATE '2019-10-04' + INTERVAL '18:17:48' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'ABC LIMITED', 'IN',  DATE '2019-10-05' + INTERVAL '23:17:48' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'ABC LIMITED', 'OUT', DATE '2019-10-06' + INTERVAL '00:17:48' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'ABC LIMITED', 'IN',  DATE '2019-10-06' + INTERVAL '09:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'ABC LIMITED', 'OUT', DATE '2019-10-06' + INTERVAL '15:00:00' HOUR TO SECOND FROM DUAL;

Query:

SELECT agency_name,
       MIN( CASE action WHEN 'IN'  THEN TIME END ) AS "IN",
       MAX( CASE action WHEN 'OUT' THEN TIME END ) AS OUT
FROM   kvtest 
GROUP BY agency_name, TRUNC( TIME )
ORDER BY agency_name, TRUNC( TIME );

Output:

| AGENCY_NAME |                   IN |                  OUT |
|-------------|----------------------|----------------------|
| ABC LIMITED | 2019-10-03T15:52:26Z | 2019-10-03T19:17:48Z |
| ABC LIMITED | 2019-10-04T15:52:26Z | 2019-10-04T18:17:48Z |
| ABC LIMITED | 2019-10-05T23:17:48Z |               (null) |
| ABC LIMITED | 2019-10-06T09:00:00Z | 2019-10-06T15:00:00Z |

SQLFiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Assuming the data is interleaved as in the question, pivoting seems way more complicated than necessary. You can just use an analytic function and filtering:

select agency_name, time as in_time, out_time
from (select k.*,
             min(case when "action" = 'OUT' then time end) over
                  (partition by agency_name
                   order by time desc
                  ) as out_time
      from kvtest k
     ) k
where "action" = 'IN';

Here is the db<>fiddle.

Upvotes: 1

Boneist
Boneist

Reputation: 23588

You can do this much more simply by using a pivot on the table without the case expressions, like so:

select agency_name,
       dt,
       "IN",
       out
from   (select agency_name,
               trunc(to_date(time, 'dd-mm-yyyy hh:mi:ss pm')) dt,
               to_date(time, 'dd-mm-yyyy hh:mi:ss pm') time,
               action,
               row_number() over (partition by agency_name, trunc(to_date(time, 'dd-mm-yyyy hh:mi:ss pm')), action order by time) rn
        from   kvtest)
pivot (max(time) for action in ('IN' as "IN", 'OUT' as "OUT"))
order by rn;

And here's the SQLFiddle to support this answer.

This works by finding the day, and then identifying the nth in and out rows in that day. Then we pivot the data, leaving the rn as one of the columns to pivot on, meaning that if there were 3 ins and outs in a day, you'd end up with 3 rows in the output.

If you want the INs and OUTs to work across days (e.g. arrive before midnight, leave after midnight), you're going to need a different solution. I have assumed in my answer that the corresponding OUT to an IN will always be on the same day.

As an aside, why is your TIME column defined as VARCHAR2? That's a bad datatype to store date or timestamp information in. I would hope that your actual table is really of DATE (or TIMESTAMP) datatype. If it isn't, I seriously suggest you look at changing the datatype to a more appropriate one.

If you simply want the earliest IN time and the latest OUT time per day, all you need is an conditional aggregate query, e.g.:

select agency_name,
       trunc(to_date(time, 'dd-mm-yyyy hh:mi:ss pm')) dt,
       min(case when action = 'IN' then to_date(time, 'dd-mm-yyyy hh:mi:ss pm') end) "IN",
       max(case when action = 'OUT' then to_date(time, 'dd-mm-yyyy hh:mi:ss pm') end) out
from   kvtest
group by agency_name,
         trunc(to_date(time, 'dd-mm-yyyy hh:mi:ss pm'));

And here's the supporting SQLFiddle.

Upvotes: 1

Related Questions