Home Oracle SQL loop LEAD() through partition
Reply: 0

Oracle SQL loop LEAD() through partition

user1136
1#
user1136 Published in April 25, 2018, 12:35 am

I have a set that looks something like this

ID  date_IN  date_out
1    1/1/18   1/2/18
1    1/3/18   1/4/18
1    1/5/18   1/8/18
2    1/1/18   1/5/18
2    1/7/18   1/9/18

I began by

SELECT ID, date_IN, Date_out, lead(date_out) over ( partition by (ID)
order by ID) as next_out
From table

And get something like this...

ID  date_IN  date_out  next_out
1    1/1/18   1/2/18   1/4/18
1    1/3/18   1/4/18   1/8/18
1    1/5/18   1/8/18    Null
2    1/1/18   1/5/18    1/9/18
2    1/7/18   1/9/18   Null

The problem I’m going to to have is that in my actual data many of the ID’s have A LOT of entries. The goal is to have all of the date_out’s appear on one row per ID....

ID  date_IN  date_out  next_out  next_out1  etc.   etc.
1    1/1/18   1/2/18   1/4/18     1/8/18     X      X
2    1/1/18   1/5/18   1/7/18     X         Null    Null

Is there a way to loop the lead() through the entire partition, order by ID drop everything but the first row then move on to the next ID?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.31393 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO