Convert rows to columns allowing duplicates

Question

Consider the following table and rows:

Listing A.

ID, name, event, type
1, 'John Doe', '2010-09-01 15:00:00.000', 'input'
1, 'John Doe', '2010-09-03 11:00:00.000', 'input'
1, 'John Doe', '2010-09-04 17:00:00.000', 'input'
1, 'John Doe', '2010-09-02 15:00:00.000', 'output'
1, 'John Doe', '2010-09-03 16:00:00.000', 'output'
1, 'John Doe', '2010-09-06 17:00:00.000', 'output'

What I want is to convert rows into columns, so I can have two different columns, input event and output event. Like:

Listing B.

ID, name, input event, output event
1, 'John Doe', '2010-09-01 15:00:00.000', '2010-09-02 15:00:00.000'
1, 'John Doe', '2010-09-03 11:00:00.000', '2010-09-03 16:00:00.000'
1, 'John Doe', '2010-09-04 17:00:00.000', '2010-09-06 17:00:00.000'

I was able to get something like following:

Listing C.

ID, name, input event, output event
1, 'John Doe', '2010-09-01 15:00:00.000', null
1, 'John Doe', '2010-09-03 11:00:00.000', null
1, 'John Doe', '2010-09-04 17:00:00.000', null
1, 'John Doe', null, '2010-09-02 15:00:00.000'
1, 'John Doe', null, '2010-09-03 16:00:00.000'
1, 'John Doe', null, '2010-09-06 17:00:00.000'

, but the problem is how to flat the rows, since the duplicate tuples ID-name ARE relevant. To convert rows into columns I usually code something like this:

select ID, name, max(case when type = 'input' then event else null end) as 'input event', max(case when type = 'output' then event else null end) as 'output event' from events group by ID, name

, but of course, the GROUP BY is going to leave out the duplicates, and that's what I don't want.

Any ideas how to achieve that with a query?

It would be nice to have a portable sql solution or for postgresql, but any idea is much appreciated.

EDIT: sorry for late answer. Both solutions from AlexRednic and Mark Bannister accomplish what I wanted. I finally opted for the second one, since it looks clearer to me. Thanks all for your answers!

You haven't indicated how to link the input and output events. ID and name alone won't do it, as there are three events of each type for the same ID and name. From the data provided, the combination of ID, name and time of day would uniquely identify a single event of each type, but would this alone be reliable with a nore representative set of data, or could there be multiple output events (possibly on multiple days) for a single ID, name and time of day? — , Sep 01 '10 at 12:47
For any input event only there can be one output event, date of output event is always older than input event. Also, after an input event there will be always an output event. There can't be two consecutive input events. My fault, the provided data set doesn't comply with this. I will correct it. — Gothmog, Sep 01 '10 at 17:36

score 2 · Accepted Answer · answered Sep 02 '10 at 09:56

Try the following:

select ID, name, event as 'input event', 
       (select min(o.event) 
        from events o 
        where o.type = 'output' and 
              i.ID = o.ID and 
              i.name = o.name and 
              i.event < o.event) as 'output event' 
from events i
where i.type = 'input'
group by ID, name, event

score 1 · Answer 2 · edited Jan 25 '11 at 17:04

1

select t1.id,t1.name,t1.event,t2.event from test t1
    inner join test t2 on t1.event <= t2.event 
        and t1.type = 'input' 
        and t2.type = 'output'
                   and t1.id = t2.id
                   and t1.name = t2.name

The thing is you need somehow to link the input/output sessions. In this query I did it by using the timestamp event column. Could you provide more information if this isn't what you wanted?

Update: now, to post-process a bit you could do

with a as
(
select t1.id,t1.name,t1.event as in_event,t2.event as out_event from test t1
    inner join test t2 on t1.event <= t2.event 
                   and t1.type = 'input' 
                   and t2.type = 'output'
                   and t1.id = t2.id
                   and t1.name = t2.name
)
select id,name,in_event,min(out_event)
       from a
group by id,name,in_event

edited Jan 25 '11 at 17:04

bluish

26,356
27
122
180

answered Sep 01 '10 at 10:03

AlexRednic

326
1
6
17

This would return 0 rows, as the input event is on a different date (but at the same time) to the output event - inputs happen on the first, outputs on the second. – Sep 01 '10 at 12:42
worked if the dates of input/output are the same as expressed in the example. That's what I said, I need more info on how the two events are linked. Is it event.input <= event.output ? – AlexRednic Sep 02 '10 at 10:28
from Gothmog's response, event.input < event.output. – Sep 02 '10 at 10:47
yeah... for some reason I doesn't show up.... I still have to get along with the code formatting – AlexRednic Sep 02 '10 at 12:07

score 0 · Answer 3 · answered Sep 01 '10 at 06:38

I'm writing from the beginning:

create table #a(
ID int,
name varchar(30),
event datetime,
type varchar(10)
)

insert #a
select  
1, 'John Doe', '2010-09-01 15:00:00.000', 'input'
union select 1, 'John Doe', '2010-09-01 16:00:00.000', 'input'
union select 1, 'John Doe', '2010-09-01 17:00:00.000', 'input'
union select 1, 'John Doe', '2010-09-02 15:00:00.000', 'output'
union select 1, 'John Doe', '2010-09-02 16:00:00.000', 'output'
union select 1, 'John Doe', '2010-09-02 17:00:00.000', 'output'

-- here is the solution sql

select 
    ID, 
    name, 
    case when type = 'input' then event else null end "input event",
    case when type = 'output' then event else null end "output event"
 from #a

I got to this step, but this is not what I want. The result I want to obtain is only three rows. The listing B. — Gothmog, Sep 01 '10 at 17:28
ok I see but listing B does not have valid groupin logic then. Can you say that as a rule "put the same dates as one row"? If you say yes, its possiple and I'll right the sql? — Burcin, Sep 02 '10 at 07:16

Convert rows to columns allowing duplicates

3 Answers3