Recursive CTE to Loop

Copper Contributor

I have table that stored name changes. I want to get the latest name as additional column. I think it might be done using CTE but I am not able to to get it working

 

OldNameNewNameYearofChange
JonJohn2021
JohnJim2022
JimJames2023
JamesJerry2024

 

 

OldNameNewNameYearofChangeMostCurrentName
JonJohn2021Jerry
JohnJim2022Jerry
JimJames2023Jerry
JamesJerry2024Jerry
1 Reply

@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

LainRobertson_0-1704637952669.png

 

Cheers,

Lain