[SOLVED] Transform Travel Table

Transform Travel Table - SQL

Table:

Student	Travel Date	Travel Location	Visits
stud1	25-03-2023	loc1	2
stud1	27-03-2023	loc2	1
stud1	24-03-2022	loc3	1
stud2	15-02-2022	loc2	3
stud3	07-07-2022	loc3	1

Above is a travel table with travel date, location and total visits to that location. The expected output is to find the travel journey mapping like below:

Output:

Student	Travel_location1	Travel_location2
stud1	loc3	loc1
stud1	loc1	loc2
stud2	loc2	null
stud3	loc3	null

stud1 has loc3 as first location based on travel date, and then comes loc1, second row continues with same logic, and also the 'travel location 2' from 1st row should be repeated as 'travel location 1' in 2nd row

Thanks for the help

Solution

DROP TABLE IF EXISTS #Travel
GO
CREATE TABLE #Travel(
  Student VARCHAR(10),
  TravelDate DATETIME,
  TravelLocation VARCHAR(50),
  Visits INT);

INSERT INTO #Travel
VALUES
  ('Stud1','2023-03-25 00:00','loc1',2),
  ('Stud1','2023-03-27 00:00','loc2',1),
  ('Stud1','2023-03-24 00:00','loc3',1),
  ('Stud2','2022-02-15 00:00','loc2',3),
  ('Stud3','2022-07-07 00:00','loc3',1);

WITH CTE1 AS (
  select Student, TravelLocation 
    ROW_NUMBER() OVER (PARTITION BY Student ORDER BY TravelDate ) AS RowNum
  from #Travel
)
SELECT 
  Travel.Student,
  Travel.TravelLocation AS TravelLocation1,
  NextTravel.TravelLocation AS TravelLocation2
FROM CTE1 Travel
LEFT JOIN CTE1 NextTravel ON Travel.Student = NextTravel.Student 
                        AND NextTravel.RowNum = Travel.RowNum+1