Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

merge two rows into one, same table .

Posted on 2014-12-08
6
Medium Priority
?
197 Views
Last Modified: 2015-01-06
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
0
Comment
Question by:dickywkt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40486337
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
 

Author Comment

by:dickywkt
ID: 40486536
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
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40487938
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:dickywkt
ID: 40488636
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
 
LVL 22

Expert Comment

by:earth man2
ID: 40506747
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
 
LVL 22

Accepted Solution

by:
earth man2 earned 2000 total points
ID: 40506801
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question