CODEWITHSUNDEEP
sql-server
data_weed
data_weed

Reputation: 19

Insert history into a new table

I'm working on this table as shown below it has name, date,id and key. I would like to insert into new table where the old_name column maintains the change in name for that key. The output result is shown below as well. Thanks

           id       name        date                    Key
            1       charles     2004-05-07              1001
            2       CON         2004-05-07              1001
            3       Virginia    2006-09-08              1001
            4       MART        2012-01-03              1001
            5       McDonalds   2013-12-30              1001


    OUTPUT 

            id   old_name          name        date                 Key
            1     NULL             charles     2004-05-07           1001
            2     charles          CON         2004-05-07           1001
            3     CON              Virginia    2006-09-08           1001
            4     Virginia         MART        2012-01-03           1001
            5     MART             McDonalds   2013-12-30           1001

Upvotes: 1

Views: 70

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

Seems like a good fit for LAG()

Select id
      ,old_name = lag(name,1) over (Partition By [Key] Order by ID)
      ,name
      ,date
      ,[key]
 From YourTable
 Order By ID

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use lag if it is SQL Server >=2012

select *, lag(name,1,null) over(order by id) Old_name from #yourhistory

Your input table

create table #yourhistory (id int, name varchar(20), date date, [key] int)

insert into #yourhistory 
  ( id  ,     name   ,     date             ,       [Key] ) values
     (    1    ,'charles  ','2004-05-07',              1001   )
    ,(    2    ,'CON      ','2004-05-07',              1001   )
    ,(    3    ,'Virginia ','2006-09-08',              1001   )
    ,(    4    ,'MART     ','2012-01-03',              1001   )
    ,(    5    ,'McDonalds','2013-12-30',              1001   )

Upvotes: 0

Related Questions