Skillfully utilizes the PARTITION group ranking increasing feature to merge consecutive identical data rows, and cleverly uses partition

Skillfully utilizes the PARTITION group ranking increasing feature to merge consecutive identical data rows, and cleverly uses partition

Problem proposal

Create some test data to explain the problem:

DECLARE @TestData TABLE(ID INT,Col1 VARCHAR(20),Col2 VARCHAR(20)) 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

Data Description: The ID column is continuously auto-incrementing. Column 1 and column 2 are status records of PBI in TFS, from what changes to what, such as newly created, approved, approved, and submitted to Shenma.

Currently, continuous and identical state change records are required to be merged, and non-consecutive or different state changes must be retained. For example:

For example, records with IDs of, are in the state from New to Approved, but record 1 is not adjacent to record 4, 5, or 6, or is not consecutive. Therefore, they must be divided into two groups,

Record 1: record 4, 5, and 6. All other records are retained because the status changes are different. The final query result should look like this:

Before proceeding, you can try it on your own. This may bring new ideas for solving problems,

Solutions

The key to this problem is that group by merges records 1, 4, 5, and 6. This does not meet the requirements and only needs to merge records 4, 5, and 6, no such field in the source table can distinguish record 1 from record 4, record 5, and record 6, which is the key to solving the problem.

Here we can use the RANK function in combination with the PARTITION keyword. First, we divide 1456 into a group and generate a new field R in the group ranking. This ranking R is very important and will be used later. See:

RANK functions are unknown.

The RANK function groups Col1 + Col2 into four groups: New-Approved, Approved-Commited, Commited-In Progress, and Approved-Removed.

In the New-Approved group, records 1, 4, 5, and 6 are ranked 1, 2, 3, and 4 respectively. Only one record is recorded in the other group, and all records are ranked 1 in the group.

Now an R field is created. The R field identifies the ranking of each record in its group, and the ranking increases from 1,

The ID auto-increment in the source table and the rank R in the group increase progressively, which is the key to solving the problem,

When consecutive records of the same type appear, their IDs and their ranking R are increasing at the same time, the difference value is the same, and the difference can easily solve the problem. See:

Records 4, 5, and 6 are the same and appear consecutively. Their IDs and rankings increase at the same time, and their differences remain unchanged, here we can use Col1 + Col2 + Gap as the grouping condition to merge records 4, 5, and 6, and then obtain a minimum ID to solve the problem. The complete script is as follows:

But what if the ID is not consecutive? This is not difficult. Refer to [MSSQL] ROW_NUMBER Function

How can I group and sort numbers in excel? Create the same sequence number for all the same data in a column of data. The sequence number must increase progressively.

Enter = INT (ROW () + 1)/3) in A2 )... Just pull down!

How can I group the numbers in excel? Create the same sequence number for all the same data in a column of data. The sequence number must increase progressively.

I can't see more rules. I guess:
A1 = 03160011
= Text (right (A1, 2)-10, "0000 ")

Drop-down Filling