I have a table with 4 columns ID, c1, c2 and LOT. ID is the primary key. For every record when c1 is 5 I want to auto-generate a number for LOT which will be a sequence starting from 1 for each distinct value of c2.

So if c1 is not 5, LOT remains null. But if c1 is 5 then for every record where c2=1 I want to populate LOT with an auto-incrementing sequence starting from 1. 
Ex:

```
ID  c1  c2  LOT
1   3   
2   5   1   1
3   5   1   2
4   5   1   3
5   4

```

Then do the same for a different value of c2. So if c2 is 2, have another bunch of auto-incrementing LOT numbers starting from 1:

```
ID  c1  c2  LOT
6   3   
7   5   2   1
8   5   1   4
9   5   2   2
10  5   2   3

```

We are using MSSQL 2014 Enterprise Ed. Would table-partitioning be useful or do I need to create special tables for each distinct value of C2?