Forum Discussion

RajuD999's avatar
RajuD999
Copper Contributor
Jan 05, 2024

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

 

OldNameNewNameYearofChange
JonJohn2021
JohnJim2022
JimJames2023
JamesJerry2024

 

 

OldNameNewNameYearofChangeMostCurrentName
JonJohn2021Jerry
JohnJim2022Jerry
JimJames2023Jerry
JamesJerry2024Jerry

1 Reply

  • LainRobertson's avatar
    LainRobertson
    Silver Contributor

    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

Resources