Take data from multiple rows to a single row

Question

I have a table that looks similar to this here:

DOCCODE  |  DOCDATE  |  STATUS    
001      | 10-OCT-13 |  START    
001      | 12-OCT-13 |  STOP    
001      | 15-OCT-13 |  START    
001      | 20-OCT-13 |  STOP    
002      | 01-NOV-13 |  START

I would like to try and have this output

DOCCODE   | STARTDATE  |  STOPDATE  | STATUS    
001       | 10-OCT-13  |  12-OCT-13 | STOP    
001       | 15-OCT-13  |  20-OCT-13 | STOP    
002       | 01-NOV-13  |            | START

Please stop posting in all uppercase - it looks like you are shouting at us. And please format your code - I'll do it this time for you. — Frank Schmitt, Oct 22 '13 at 07:51
possible duplicate of [SQL Server 2005, turn columns into rows](http://stackoverflow.com/questions/428041/sql-server-2005-turn-columns-into-rows) — Frank Schmitt, Oct 22 '13 at 07:54
Voted for close - the GROUP BY answer in the linked question should work for any RDBMS (doesn't require PIVOT or similar). — Frank Schmitt, Oct 22 '13 at 07:55

score 0 · Answer 1 · answered Oct 22 '13 at 07:58

You can achieve this with a self (outer) join

SELECT start.doccode AS doccode, 
       start.docdate AS startdate, 
       stop.docdate AS stopdate, 
       COALESCE(stop.status, start.status) AS status
FROM   my_table start
LEFT OUTER JOIN my_table stop ON start.doccode = stop.doccode
WHERE  start.doccode = 'START' AND stop.doccode = 'STOP'

Andrey Morozov · Answer 2 · 2013-10-23T08:52:28.927

Try this:

select sr.DOCCODE
, sr.DOCDATE [STARTDATE]
, x.DOCDATE [STOPDATE]
, ISNULL(x.[STATUS], sr.[STATUS]) [STATUS]
from my_table sr
outer apply (
    select top 1 *
    from my_table sp
    where [STATUS] = 'STOP'
    and DOCDATE > sr.DOCDATE
    and DOCCODE = sr.DOCCODE
    order by DOCDATE) x
where sr.[STATUS]  = 'START'

Results:

DOCCODE STARTDATE   STOPDATE    STATUS
001     2013-10-10  2013-10-12  STOP
001     2013-10-15  2013-10-20  STOP
002     2013-11-01  NULL        START

SQL Fiddle

UPDATE

Oracle version (using correlated subqueries instead of cross apply):

select sr.DOCCODE
, sr.DOCDATE STARTDATE
, (
    select min(DOCDATE)
    from my_table sp
    where STATUS = 'STOP'
    and DOCDATE > sr.DOCDATE
    and DOCCODE = sr.DOCCODE) as STOPDATE
, NVL((
    select min(STATUS)
    from my_table sp
    where STATUS = 'STOP'
    and DOCDATE > sr.DOCDATE
    and DOCCODE = sr.DOCCODE), sr.STATUS) as STATUS
from my_table sr
where sr.STATUS  = 'START'

SQL Fiddle (Oracle)

Whether this code work in Oracle Sql Developer – user2906098 Oct 23 '13 at 04:46 — user2906098, Oct 23 '13 at 04:46
See update in the post – Andrey Morozov Oct 23 '13 at 08:40 — Andrey Morozov, Oct 23 '13 at 08:40

score 0 · Answer 3 · answered Oct 23 '13 at 08:45

SELECT s1.doccode AS doccode, 
   s1.docdate AS startdate, 
   s2.docdate AS stopdate, 
   isnull(s2.status, s1.status) AS status
FROM   my_table s1
LEFT OUTER JOIN my_table s2 ON s1.doccode = s2.doccode
WHERE  s1.doccode = 'START' AND s2.doccode = 'STOP'

Take data from multiple rows to a single row

3 Answers3