CODEWITHSUNDEEP
sql-servert-sqlssis
Ranjith Varatharajan
Ranjith Varatharajan

Reputation: 1694

Add incremental number in duplicate records

I have SSIS package, which retrieves all records including duplicates. My question is how to add an incremental value for the duplicate records (only the ID and PropertyID).

Eg

Records from a Merge Join

ID   Name   PropertyID  Value
1    A      1           123
1    A      1           223
2    B      2           334
3    C      1           22
3    C      1           45

Now I need to append an incremental value at the end of the each record as

ID   Name   PropertyID  Value  RID
1    A      1           123    1
1    A      1           223    2
2    B      2           334    1
3    C      1           22     1
3    C      1           45     2

Since ID 1 & 3 are returned twice, the first record has RID as 1 and the second record as 2.

ID and PropertyID need to be considered to generate the Repeating ID i.e RID.

How can I do it in SSIS or using SQL command?

Update #1:

enter image description here

Please correct me if I'm wrong, since the data is not stored in any table yet, I'm unable to use the select query using rownumber(). Any way I can do it from the Merge Join?

Upvotes: 2

Views: 2506

Answers (2)

DEEPAK LAKHOTIA
DEEPAK LAKHOTIA

Reputation: 993

This will do the job for you: https://paultebraak.wordpress.com/2013/02/25/rank-partitioning-in-etl-using-ssis/

You will need to write a custom script, something like this:

public
class
ScriptMain : UserComponent

{
 string _sub_category = “”;
int _row_rank = 1;

public  override  void Input0_ProcessInputRow(Input0Buffer Row)
{
if (Row.subcategory != _sub_category)
{
_row_rank = 1;
Row.rowrank = _row_rank;

_sub_category = Row.subcategory;
}
else
{
   _row_rank++;
   Row.rowrank = _row_rank;

}

}
}

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

You could use ROW_NUMBER:

SELECT ID, 
       Name, 
       PropertyID, 
       Value, 
       ROW_NUMBER() OVER(PARTITION BY ID, PropertyID ORDER BY Value) As RID
FROM TableName

Upvotes: 6

Related Questions