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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.