Solved

merge two rows into one, same table .

Posted on 2014-12-08
6
192 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 500 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 Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

739 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