Forum Discussion
RajuD999
Jan 05, 2024Copper Contributor
Recursive CTE to Loop
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
OldName | NewName | YearofChange |
Jon | John | 2021 |
John | Jim | 2022 |
Jim | James | 2023 |
James | Jerry | 2024 |
OldName | NewName | YearofChange | MostCurrentName |
Jon | John | 2021 | Jerry |
John | Jim | 2022 | Jerry |
Jim | James | 2023 | Jerry |
James | Jerry | 2024 | Jerry |
1 Reply
Sort By
- LainRobertsonSilver Contributor
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