This article is based on ROW_NUMBER()Over(Partition By Column1 Order By Column1)). To understand the user of this statement let us take an example or the goal we want to achive. I have a list of student name with their roll number. As shown below
Now what is our goal is to have result in which each student name must have the repeatative count appended to their name. As shown below.
Any guy can do this using the cursor and iterate each row along with this maintaining count of each student at each iteration and bla bla bla..
This is the most common answer or solution that any one will suggest. But the easieast and best way to do this is by using ROW_NUMBER()Over(Partition By Column1 Order By Column1)) statement.
Following is the table structure with insert statement for #student temporary table.
CREATE TABLE #student
rollnumber INT ,
INSERT INTO #student
( rollnumber ,
SELECT 1 ,
SELECT 2 ,
SELECT 3 ,
SELECT 4 ,
SELECT 5 ,
SELECT 6 ,
SELECT 7 ,
SELECT 8 ,
Following is the single query which do this with a singleshot.
SELECT rollnumber ,
name + '-'
+ CAST(( ROW_NUMBER() OVER ( PARTITION BY name ORDER BY rollnumber, name ) ) AS VARCHAR(MAX)) ,
ROW_NUMBER() OVER ( PARTITION BY name ORDER BY rollnumber, name ) AS 'Repeatative Count'
ORDER BY rollnumber