Reuse last value in row and make first value on next - SQL server

Question

So, I have this SQL table:

Traveller      checkin       dateTime
5566           Madrid        2017-01-01 01:00:00.00
5566           Barcelona     2017-01-02 03:00:00.00
5566           Berlin        2017-01-03 02:00:00.00
5566           Paris         2017-01-06 05:00:00.00
5566           London        2017-01-07 06:00:00.00
5566           Madrid        2017-01-08 02:00:00.00
4422           Moscow        2017-01-03 08:00:00.00
4422           Madrid        2017-01-04 07:00:00.00
4422           Barcelona     2017-01-05 03:00:00.00
8833           Barcelona     2017-02-01 08:00:00.00
8833           Berlin        2017-02-02 04:00:00.00
8833           London        2017-02-03 01:00:00.00
8833           Berlin        2017-02-03 22:00:00.00
9966           Paris         2017-02-03 04:00:00.00
9966           London        2017-02-04 06:00:00.00
9966           Berlin        2017-02-05 01:00:00.00
...            ...           ...

Is it possible to somehow order these into a from-to table, using all but the first checkin as both a from and to destination. Like this:

Traveller      From       To
5566           Madrid     Barcelona
5566           Barcelona  Berlin
5566           Berlin     Paris
5566           Paris      London
5566           London     Madrid
4422           Moscow     Madrid
4422           Madrid     Barcelona
8833           Barcelona  Berlin
8833           Berlin     London
8833           London     Berlin
...            ...        ...

I know the basics of SQL but I'm still learning, so if anyone can think of a way this could be done (or not), help me understand how it works.

Thanks a lot!

Tables have no inherent order. I'm *hoping* there's some additional column(s) by which you're attempting to *define* an order? — Damien_The_Unbeliever, Jun 02 '17 at 06:52
SQL Server 2008 is no longer supported. It's a 10-year old version. The earliest supported version is 2012 wich makes this query relatively trivial using ranking and window functions — Panagiotis Kanavos, Jun 02 '17 at 07:12
@PanagiotisKanavos you can have SQL Server 2016 installed and the same time have very old compatibility level. So I really miss your point on `no longer supported`. But TS mentioned it clearly, then why arguing?) — pkuderov, Jun 02 '17 at 07:37
@pkudero check the tag again. The point is - finding the *docs* for old versions is getting harder. — Panagiotis Kanavos, Jun 02 '17 at 07:38

Damien_The_Unbeliever · Answer 1 · 2017-06-02T07:05:42.167

2

You can use ROW_NUMBER() to assign sequential numbers to each traveller's checkins, in a CTE, and then join the CTE to itself to create the result:

declare @t table (Traveller int not null, checkin varchar(19) not null,
                  TimeStamp datetime2 not null)
insert into @t (Traveller,checkin,TimeStamp) values
(5566,'Madrid',   '2017-06-02T07:56:01'),
(5566,'Barcelona','2017-06-02T07:56:02'),
(5566,'Berlin',   '2017-06-02T07:56:03'),
(5566,'Paris',    '2017-06-02T07:56:04'),
(5566,'London',   '2017-06-02T07:56:05'),
(5566,'Madrid',   '2017-06-02T07:56:06'),
(4422,'Moscow',   '2017-06-02T07:56:07'),
(4422,'Madrid',   '2017-06-02T07:56:08'),
(4422,'Barcelona','2017-06-02T07:56:09'),
(8833,'Barcelona','2017-06-02T07:56:10'),
(8833,'Berlin',   '2017-06-02T07:56:11'),
(8833,'London',   '2017-06-02T07:56:12'),
(8833,'Berlin',   '2017-06-02T07:56:13'),
(9966,'Paris',    '2017-06-02T07:56:14'),
(9966,'London',   '2017-06-02T07:56:15'),
(9966,'Berlin',   '2017-06-02T07:56:16')

;With Numbered as (
    select
        *,
        ROW_NUMBER() OVER (PARTITION BY Traveller ORDER by TimeStamp) as rn
    from @t
)
select
    n1.Traveller,n1.checkin,n2.checkin
from
    Numbered n1
        inner join
    Numbered n2
        on
            n1.Traveller = n2.Traveller and
            n1.rn = n2.rn - 1
order by
    n1.Traveller,n1.rn

Results:

Traveller   checkin             checkin
----------- ------------------- -------------------
4422        Moscow              Madrid
4422        Madrid              Barcelona
5566        Madrid              Barcelona
5566        Barcelona           Berlin
5566        Berlin              Paris
5566        Paris               London
5566        London              Madrid
8833        Barcelona           Berlin
8833        Berlin              London
8833        London              Berlin
9966        Paris               London
9966        London              Berlin

edited Jun 02 '17 at 07:05

answered Jun 02 '17 at 07:00

Damien_The_Unbeliever

234,701
27
340
448

Isn't ROW_NUMBER a 2012 function? Why not use LEAD in that case? – Panagiotis Kanavos Jun 02 '17 at 07:22
@PanagiotisKanavos - no, ROW_NUMBER() has existed at least as far back as 2008. Trying to remember if it was in 2005. – Damien_The_Unbeliever Jun 02 '17 at 07:23
The docs no longer mention versions before 2012. Do you have a link? – Panagiotis Kanavos Jun 02 '17 at 07:23
@PanagiotisKanavos - here's a question [asking about it's use in 2005](https://stackoverflow.com/a/4812038/15498) – Damien_The_Unbeliever Jun 02 '17 at 07:25
[This link](https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql) shows `SQL Server (starting with 2008)` – Panagiotis Kanavos Jun 02 '17 at 07:25
@PanagiotisKanavos - the Microsoft documentation has always taken a revisionist approach and as they move through pages updating them, they tend to change the "supported from" to the earliest *supported* version of the product. It can't be relied upon as historical truth. – Damien_The_Unbeliever Jun 02 '17 at 07:28
I remember using it on 2008 R2 so at least this version :) – pkuderov Jun 02 '17 at 07:28
@Damien_The_Unbeliever I'm not talking about historical truth, I'm talking about finding what works where. I don't have BOL 2008 installed `learn.microsoft.com` seems better in this regard – Panagiotis Kanavos Jun 02 '17 at 07:29
1

@PanagiotisKanavos - just run this on one of our servers - `select top 1 @@VERSION,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY object_id) from sys.objects` - result - `Microsoft SQL Server 2005 - 9.00.5057.00 (X64), 1` – Damien_The_Unbeliever Jun 02 '17 at 07:32

pkuderov · Answer 2 · 2017-06-02T07:33:43.030

You can use row_number window function. It's Sql Server 2008 way to handle this problem. Here it is:

;with raw_data (id, city, arrival) as (
    select 5566, 'Madrid', '2017-01-01 01:00:00.00' union all
    select 5566, 'Barcelona', '2017-01-02 03:00:00.00' union all
    select 5566, 'Berlin', '2017-01-03 02:00:00.00' union all
    select 5566, 'Paris', '2017-01-06 05:00:00.00' union all
    select 5566, 'London', '2017-01-07 06:00:00.00' union all
    select 5566, 'Madrid', '2017-01-08 02:00:00.00' union all
    select 4422, 'Moscow', '2017-01-03 08:00:00.00' union all
    select 4422, 'Madrid', '2017-01-04 07:00:00.00' union all
    select 4422, 'Barcelona', '2017-01-05 03:00:00.00' union all
    select 8833, 'Barcelona', '2017-02-01 08:00:00.00' union all
    select 8833, 'Berlin', '2017-02-02 04:00:00.00' union all
    select 8833, 'London', '2017-02-03 01:00:00.00' union all
    select 8833, 'Berlin', '2017-02-03 22:00:00.00' union all
    select 9966, 'Paris', '2017-02-03 04:00:00.00' union all
    select 9966, 'London', '2017-02-04 06:00:00.00' union all
    select 9966, 'Berlin', '2017-02-05 01:00:00.00'
)
, arrivals as (
    select
        id, city, arrival,
        row_number() over (partition by id order by arrival) as rn
    from raw_data
)
, flies as (
    select
        fr.id,
        fr.city as [from],
        fr.arrival as [departure],
        [to].city as [to],
        [to].arrival as [arrival]
    from arrivals fr
    join arrivals [to] on
        fr.id = [to].id
        and [to].rn = fr.rn + 1
)
select
    *
from flies

SQL Server 2012 and higher

But if you have Sql Server 2012 and higher you can use solve it with lag function, which gives access to some previous row in current.

E.g. lag(colX, N, <default>) means you got N-th previous value of column colX or value if there isn't. It's just we need! Strictly the previous one:

;with raw_data (id, city, arrival) as (
    -- omitted for the sake of shortness :)
)
, flies as (
    select
        id,
        -- here it is!!! prev city and arrival
        lag(city, 1, null) over (partition by id order by arrival) as [from],
        lag(arrival, 1, null) over (partition by id order by arrival) as [departure],
        city as [to],
        arrival as [arrival]
    from raw_data
)
select
    *
from flies
where
    -- and here we take only rows from where we're 'departured'
    [from] is not null

@Katalo the same part from first query - filling example data. It just so many duplicate lines so I omitted them. And sorry for renaming you original columns - just missed that in a hurry( — pkuderov, Jun 02 '17 at 07:38

Panagiotis Kanavos · Accepted Answer · 2017-06-02T07:58:14.057

You can use the LEAD function to return the next row's value, eg

select 
    Traveller,
    checkin,
    LEAD(checkin,1) OVER(PARTITION by traveller order by timestamp) as destination
from @t t

This will return a NULL destination for the last leg. To remove this, you can use a CTE to filter the data:

with checkins as (
    select 
        Traveller,
        checkin,LEAD(checkin,1) OVER(PARTITION by traveller order by timestamp) as destination
    from @t t)
select * from checkins 
where destination is not null

To test all of this :

declare @t table (Traveller int not null, checkin varchar(19) not null, TimeStamp datetime2 not null);

insert into @t (Traveller,checkin,TimeStamp) values
(5566,'Madrid',   '2017-06-02T07:56:01'),
(5566,'Barcelona','2017-06-02T07:56:02'),
(5566,'Berlin',   '2017-06-02T07:56:03'),
(5566,'Paris',    '2017-06-02T07:56:04'),
(5566,'London',   '2017-06-02T07:56:05'),
(5566,'Madrid',   '2017-06-02T07:56:06'),
(4422,'Moscow',   '2017-06-02T07:56:07'),
(4422,'Madrid',   '2017-06-02T07:56:08'),
(4422,'Barcelona','2017-06-02T07:56:09'),
(8833,'Barcelona','2017-06-02T07:56:10'),
(8833,'Berlin',   '2017-06-02T07:56:11'),
(8833,'London',   '2017-06-02T07:56:12'),
(8833,'Berlin',   '2017-06-02T07:56:13'),
(9966,'Paris',    '2017-06-02T07:56:14'),
(9966,'London',   '2017-06-02T07:56:15'),
(9966,'Berlin',   '2017-06-02T07:56:16');



with checkins as (
    select 
        Traveller,
        checkin,LEAD(checkin,1) OVER(PARTITION by traveller order by timestamp) as destination
    from @t t)
select * from checkins 
where destination is not null

Will return :

Traveller   checkin             destination
----------- ------------------- -------------------
4422        Moscow              Madrid
4422        Madrid              Barcelona
5566        Madrid              Barcelona
5566        Barcelona           Berlin
5566        Berlin              Paris
5566        Paris               London
5566        London              Madrid
8833        Barcelona           Berlin
8833        Berlin              London
8833        London              Berlin
9966        Paris               London
9966        London              Berlin

Whilst we may encourage people to move to supported versions, the sad reality is that people do continue to work with older versions. As such, since the OP has tagged specifically as 2008, this won't solve their problem. — Damien_The_Unbeliever, Jun 02 '17 at 07:26
@Damien_The_Unbeliever the Technet docs don't show results for unsupported versions unless you explicitly type the version number in the URL. Finding what works in these versions is not that easy — Panagiotis Kanavos, Jun 02 '17 at 07:27
Hey, sorry for the inconvenience, but its a SQL server 2012 im running — Katalo, Jun 02 '17 at 07:34
@Katalo if you check the different SQL queries you'll see why this is a significant difference. Affects performance too — Panagiotis Kanavos, Jun 02 '17 at 07:36

Reuse last value in row and make first value on next - SQL server

3 Answers3