Clever use of partition Group ranking incremental feature to resolve merging consecutive identical data rows _mssql

Questions raised

First create some test data to illustrate the topic:

DECLARE @TestData TABLE (ID int,col1 VARCHAR (), Col2 VARCHAR) 
INSERT into @TestData (id,col1,col2) 
SELECT 1 , ' New ', ' Approved ' union ALL 
Select 2, ' Approved ', ' commited ' union ALL 
Select 3, ' commited ', ' in Progress ' union All 
Select 4, ' new ', ' Approved ' union ALL 
Select 5, ' new ', ' Approved ' union ALL 
Select 6, ' new ', ' Approved ' UNION all 
Select 7, ' Approved ', ' Removed ' 
select * from @TestData

The data shows that the ID column is continuously growing, and columns 1 and 2 are PBI status records in TFS, that is, from what changes to what, such as new to approval, approval to the submission of God Horse's

Continuous and identical state change records are now required to be merged, discontinuous, or different state change reservations, for example:

For example, the 1,4,5,6 records are from new to approved state, but record 1 is not adjacent to records 4, 5, 6, or discontinuous, then it's going to be divided into two groups,

Records 11 groups, records 4, 5, 61 groups, other records because the state changes are different so all retain, the final query results should grow into the following image:

Before you go on, you can try it yourself, which may lead to new ways to solve problems.

Ideas for solving problems

The key to this problem is that the group by Will record 1, 4, 5, 6 merged together, and this does not meet the requirements, only need to merge 4, 5, 6, the source table does not have such a field can record 1 and records 4, 5, 6 distinguish between, this is the key to solving problems

Here you can use the Rank function with the partition keyword, first put 1456 to a group, and create a group of the new field R, the ranking R is critical, the following will be used, see figure:

The rank function does not understand the point here

The rank function is grouped under Col1 + Col2, which is divided into four groups, namely New-approved, approved-commited, commited-in Progress, approved-removed

In the New-approved group, records 1, 4, 5, 6 were ranked 1, 2, 3, 4, and only one record in the other group, ranked 1 in the group.

Now we've created a R field that identifies the rank of each record in its group, starting at 1 increments,

The source table ID from the increase in the group ranked R increment, this is the key to solving problems,

When the same continuous record appears, its ID and its rank r at the same time increment, the difference is the same, get this difference can easily solve the problem, look at the following figure:

Record 4, 5, 6 the same and continuous appearance, its ID and its ranking at the same time growth, the difference is kept unchanged, where the use of Col1 + Col2 + gap as a grouping condition can be records 4, 5, 6 merge, and then take a minimum ID out, the problem solved, complete script as follows:

But what if the ID is not contiguous? This is not difficult, reference [Mssql]row_number function