看起来你想把相邻的行转换成一个范围，所以如果每个用户代码有10行，你想生成9个输出行，每行都有当前和“下一个”ApplicationID值，你可以使用LEAD或LAG函数来完成。
根据该数据：

declare @TEMP table(
    ID INT,
    ApplicationId INT,
    usercode NVARCHAR(50)
)

insert into @temp (ID,ApplicationId,usercode)
values
(43461,34,'john'),
(43462,52,'john'),
(43463,55,'john');

以下查询将生成所需的结果：

with a as(
SELECT usercode,
    row_number() over (partition by usercode order by id) as RN,
    applicationid,
    lag(applicationid) over (partition by usercode order by id) as Prev
from @temp)
SELECT 
    usercode,
    rn-1 as ID_1 ,
    prev as data_1,
    rn as ID_2,
    applicationid as data_2
from a
where prev is not null

这将返回

usercode    ID_1    data_1  ID_2    data_2
john        1       34      2       52
john        2       52      3       55

over (partition by usercode order by id)按照usercode对数据进行分区，并按照ID对数据进行排序，然后我们可以计算分区中的行号，并使用LAG或LEAD检索上一个或下一个值。
LAG/LEAD将在边缘处返回NULL，因此prev is not null用于排除边缘对。

因为你要做的是为每个用户代码获取n-1行，你可以使用LEAD()来获取下一个值，当它为空时（即最后一行），可以省略它。例外的情况是，如果一个用户只有一行，你可以通过总是包含每个用户的第一行来避免这种情况。所以你最终会得到这样的结果：

WITH YourData AS
(   SELECT  ID, ApplicationID, UserCode
    FROM    (VALUES 
                (43641, 34, 'John'),(43642, 52, 'John'),(43643, 55, 'John'), (43648, 55, 'Bill'),
                (43645, 34, 'Steve'),(43646, 52, 'Steve'),(43647, 55, 'Steve'),(43648, 56, 'Steve')
            ) AS d (ID, ApplicationID, UserCode)
)
SELECT  d.UserCode, 
        ID_1 = d.RowNumber, 
        Data_1 = d.ApplicationID,
        ID_2 = CASE WHEN d.NextApplicationID IS NULL THEN NULL ELSE d.RowNumber + 1 END,
        Data_2 = d.NextApplicationID
FROM    (   SELECT  d.ID, 
                    d.ApplicationID, 
                    d.UserCode,
                    RowNumber = ROW_NUMBER() OVER(PARTITION BY d.UserCode ORDER BY d.ApplicationID),
                    NextApplicationID = LEAD(d.ApplicationID) OVER(PARTITION BY d.UserCode ORDER BY d.ApplicationID)
            FROM    YourData AS d
        ) AS d
WHERE   d.NextApplicationID IS NOT NULL
OR      d.RowNumber = 1;

其中：
| 用户代码|识别码_1|数据_1|身份证_2|数据_2|
| - ------|- ------|- ------|- ------|- ------|
| 比尔|1个|五十五|零|零|
| 约翰|1个|三十四|第二章|五十二|
| 约翰|第二章|五十二|三个|五十五|
| 史蒂夫|1个|三十四|第二章|五十二|
| 史蒂夫|第二章|五十二|三个|五十五|
| 史蒂夫|三个|五十五|四个|五十六|

**一个

看起来很简单，除非我忽略了什么，您希望显示按id排序的行-可能在同一个user code中，然后显示应用程序ID、某个序列号、下一行的应用程序ID和加1的序列号，并且您不希望显示同一个usercode中没有后继行的行。

WITH
indata(id,appid,usercode) AS (
            SELECT 43461,34,'john'
  UNION ALL SELECT 43462,52,'john'
  UNION ALL SELECT 43463,55,'john'
)
,
olap AS (
  SELECT
    usercode
  , ROW_NUMBER() OVER(PARTITION BY usercode ORDER BY id)   AS id_1
  , appid                                                  AS data_1
  , ROW_NUMBER() OVER(PARTITION BY usercode ORDER BY id)+1 AS id_2
  , LEAD(appid)  OVER(PARTITION BY usercode ORDER BY id)   AS data_2
  FROM indata
)
SELECT
  *
FROM olap
WHERE data_2 IS NOT NULL;
-- out  usercode | id_1 | data_1 | id_2 | data_2 
-- out ----------+------+--------+------+--------
-- out  john     |    1 |     34 |    2 |     52
-- out  john     |    2 |     52 |    3 |     55