merge two rows into one, same table .

I would like to merge two rows into one.  Here is a small example of what I mean.
Initial data:
id | room | action | time
1  |  A    |  IN       |   2014-12-1 8:00
1  |  A    |  OUT    |  2014-12-1 9:00
1  |  B    |  IN       |  2014-12-1 9:10
1  |  B    |  OUT   |  2014-12-1 10:00
2  |  A    |  IN       |   2014-12-1 8:00
2  |  A    |  OUT   |  2014-12-1 9:00

merge to:
id | room | IN              |      OUT
1  |  A    |  2014-12-1 8:00  | 2014-12-1 9:00
1  |  B    |  2014-12-1 9:10  | 2014-12-1 10:00
2  |  A    |  2014-12-1 8:00  | 2014-12-1 9:00

What's the best way.
Thanks in advance
dickywktAsked:
Who is Participating?
 
earth man2Commented:
thinking about this a bit more you can enforce a select of valid data like this

select * from
 (select room,id,action,time as time_in,
 lead(time, 1,'infinity'::timestamp) over w as time_out,
 lead(action::text, 1, (CASE when action='IN' then 'OUT' else 'IN' END)::text) over w as action_out
from roomuse
WINDOW w as ( partition by room,id  order by time )
as foo where action = 'IN' and action_out = 'OUT';
 room | id | action |       time_in       |      time_out       | action_out
------+----+--------+---------------------+---------------------+------------
 A    |  1 | IN     | 2014-12-01 08:00:00 | 2014-12-01 09:00:00 | OUT
 A    |  1 | IN     | 2014-12-01 11:00:00 | 2014-12-01 11:15:00 | OUT
 A    |  2 | IN     | 2014-12-01 08:00:00 | 2014-12-01 09:00:00 | OUT
 B    |  1 | IN     | 2014-12-01 09:10:00 | 2014-12-01 10:00:00 | OUT
(4 rows)

Open in new window

0
 
Haris DulicCommented:
Hi,

try like this:

select id, room , min(IN) as IN, min(out) as OUT
from (
select id, room, case when action ='IN' then time else sysdate end AS iN , case when time='OUT' then time else sysdate end as out
from table_name
) a 
group by id, room
order by id, room

Open in new window

0
 
dickywktAuthor Commented:
Thanks Haris

I try:
select id, room , max(in1) as IN, max(out1) as OUT
from (
select id, room, time, action,
case when action ='IN' then time else '1970-1-1'  end AS in1 ,
case when action='OUT' then time else '1970-1-1' end as out1
from roomuse
) a
group by id, room  
order by id, room  

it can give correct result basic on my last simple example, but I may give a too simple test data.  If more data as:
id | room | action | time
1  |  A    |  IN       |   2014-12-1 8:00
1  |  A    |  OUT    |  2014-12-1 9:00
1  |  B    |  IN       |  2014-12-1 9:10
1  |  B    |  OUT   |  2014-12-1 10:00
2  |  A    |  IN       |   2014-12-1 8:00
2  |  A    |  OUT   |  2014-12-1 9:00
1  |  A    | IN         | 2014-12-1 11:00
1  |  A    | OUT     | 2014-12-1 11:15

the command will not show the record of "1 whom IN the A at  2014-12-1 8:00"
Is it any simple way to correct it?
Thanks
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Haris DulicCommented:
Hi,

here is the code:

select a.id, a.room, a.time as in , b.time as out 
from (
select ROW_NUMBER() over (order by id) as row_id,
id, room, time
from roomuse
where action='IN'
  ) a left join 
(
select ROW_NUMBER() over (order by id) as row_id,
id, room, time
from roomuse
where action='OUT'
  ) b on a.row_id=b.row_id
order by 1,2

Open in new window


You can test it here: http://sqlfiddle.com/#!15/7f1fc/18
0
 
dickywktAuthor Commented:
Hi,
From the idea of your code, I amend it as:

select DISTINCT ON (a.id, a.room, a.time) a.id, a.room, a.time, b.time1
from
(select * from roomuse
where action = 'IN' ) a
left outer join
(
select id, room, min(time) time1 from roomuse
where  action = 'OUT'
group by id, room, time
) b
on a.id= b.id and a.room=b.room and a.time< b.time1
group by a.id, a.room, a.time, b.time1
Order by a.id, a.room, a.time

it get the result what I need while the data in more general case.  But when there are many data, the effeciency is not good, it may due to need to join all records for a.time< b.time1.  I want to see if there are any way to improve ?
0
 
earth man2Commented:
if you can guarantee that you always have an OUT record for every IN record before another IN record is defined then ..

postgres=# select room,id,time_in,time_out from
(select room,id,action,time as time_in,
lead(time, 1,localtimestamp) over( partition by room,id  order by time ) as time_out
from roomuse ) as foo
where action = 'IN';

 room | id |       time_in       |      time_out
------+----+---------------------+---------------------
 A    |  1 | 2014-12-01 08:00:00 | 2014-12-01 09:00:00
 A    |  1 | 2014-12-01 11:00:00 | 2014-12-01 11:15:00
 A    |  2 | 2014-12-01 08:00:00 | 2014-12-01 09:00:00
 B    |  1 | 2014-12-01 09:10:00 | 2014-12-01 10:00:00
(4 rows)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.