How to use LEAD in SQL Server 2008

Question

I have this script and it works in SQL Server 2012, but I need to use it in SQL Server 2008. Does anyone have a suggestion ?

Create table #TempOne (ID int)

Insert into #TempOne 
Values (1), (2), (34), (121), (72), (34), (81), (26), (234)

Create table #TempTwo 
(
    ID int,
    Name Varchar(30),
    Age int
)

Insert into #TempTwo  
Values (18, 'P', 291), (11, 'P', 21), (13, 'P', 11), (21, 'P', 21)

Select 
    LEAD(ID,3) Over(Order By ID) As ID,  Name , Age  
From 
    (Select ID, NULL As Name, NULL As Age
     From #TempOne
     Union
     Select NULL, Name, age  
     From #TempTwo) a

Did you my answer solve your problem? If so, could you take the time to accept my answer to indicate that? You can accept an answer by checking the checkmark (✔) next to the answer. — TT., Jan 12 '18 at 12:08

score 0 · Answer 1 · answered Jan 08 '18 at 04:42

Your query would look something like the following:

WITH a AS (
    Select ID , NULL As Name,NULL As Age
    From #TempOne
    Union
    Select NULL, Name,age  From #TempTwo
),
an AS (
    SELECT
        *,
        rn=ROW_NUMBER() OVER (ORDER BY ID)
    FROM
        a
)
SELECT
    an_r.ID,an_l.Name,an_l.Age
FROM 
    an AS an_l
    LEFT JOIN an AS an_r ON
        an_r.rn=an_l.rn+3;

What this does, is first number the rows using ROW_NUMBER, ordered by ID. In that case, LEAD(ID,3) can be done by self-joining the numbered set on right-rn=left-rn+3.

How to use LEAD in SQL Server 2008

1 Answers1