How to merge Cosecutive duplicate status rows with different values in columns - Sql Server

My Current Table:

Output should be



declare @table table (
        ProductID int, TransactionID int, Details varchar(50), Status varchar(100), 
        StatusDate datetime, RequestDate datetime )

insert into @table 
        Select 1, 11101, 'Enroll', 'Received'                           , '10/2/2010', '10/01/2010' union all
        Select 1, 11102, 'Enroll', 'Failed: Invalid Address', '10/4/2010', '10/03/2010' union all
        Select 1, 11105, 'Update', 'Failed: Invalid Address', '10/5/2010', '10/04/2010' union all
        Select 1, 11108, 'Update', 'Failed: Invalid Address', '10/6/2010', '10/05/2010' union all
        Select 1, 11110, 'Update', 'Shipped' , '10/11/2010', '10/10/2010' union all
        Select 1, 11111, 'Enroll', 'Received'                           , '9/11/2010',  '9/10/2011' union all
        Select 1, 11113, 'Update', 'Failed: Invalid Address', '9/13/2011',  '9/12/2011' union all
        Select 1, 11117, 'Update', 'Failed: Invalid Address', '9/14/2011',  '9/13/2011' union all
        Select 1, 11120, 'Update', 'Shipped'                            , '9/21/2011',  '9/20/2011'
         ;With CTE As (
        Select *, (Row_Number() over(order by ProductID, TransactionID, StatusDate desc))as RowNum
        from @table ),
CTE2 As (
        Select CTE.*, (case When CTE.Status = isnull((select t.Status from CTE t where t.RowNum = (CTE.RowNum + 1)), '')then 1
        else 0 end
        )as RowNum2
        from CTE )Select CTE2.ProductID, CTE2.TransactionID, CTE2.Details, CTE2.Status, CTE2.StatusDate, CTE2.RequestDate
from CTE2 where RowNum2 = 0    

Insert Data in Auto Incremented column Table

--Stop Identity of table and then insert data whichever we need and again start identitiy....
SET IDENTITY_INSERT tablename ON

insert into tablename(id,name,descr)
select id,name,descr
from tablename

SET IDENTITY_INSERT tablename OFF

Delete duplicate records from Table

If any time, we want to delete duplicate records from table than we can use following query:


tableName - Our SQL table name
idtablename - Autoincremented Primary Key
columnname - By which we can identify the duplicate value in Table.



DELETE tablename WHERE idtablename in(    
    SELECT idtablename FROM tablename,(
    SELECT columnname,MIN(idtablename) AS minID
    FROM  tablename
    GROUP BY columnname
    HAVING COUNT(*) >1)  c
    WHERE c.columnname = tablename.columnname  
                   AND idtablename > minID)