Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

merge two rows into one, same table .

Posted on 2014-12-08
6
Medium Priority
?
200 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
Loops Section Overview
Suggested Courses

773 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