Recursive CTE to Loop

RajuD999 

Hi.

An old-fashioned self-join can achieve this. Here's a demonstration based on your example data.

Demonstration script including dummy data

DROP TABLE IF EXISTS [table1]
GO

-- Demo table.
CREATE TABLE [table1]
(
	[id] [int] NOT NULL
	, [oldname] [varchar](32) NOT NULL
	, [newname] [varchar](32) NOT NULL
	, [yearofchange] [int] NOT NULL
)
GO

-- Demo table data.
INSERT INTO
	[table1]
VALUES
	(1, 'Jon', 'John', 2021)
	, (1, 'John', 'Jim', 2022)
	, (1, 'Jim', 'James', 2023)
	, (1, 'James', 'Jerry', 2024)
GO

-- Demo query using a self-join.
SELECT
	[a].[oldname]
	, [a].[newname]
	, [a].[yearofchange]
	, [b].[newname] AS [current]
FROM
	[table1] AS [a] INNER JOIN
	(
		SELECT
			ROW_NUMBER() OVER (
				PARTITION BY
					[id]
				ORDER BY
					[yearofchange] DESC
			) AS [order]
			, [id]
			, [newname]
		FROM
			[table1]
	) AS [b] ON
		[a].[id] = [b].[id]
		AND [b].[order] = 1
GO

Demonstration output

Cheers,

Lain