SQL Server or SSIS code for incrementing

Question

Input

ID
12345
12346
12346
12387
12568
12387
12387

Output

ID     -  Value
12345  -  1
12346  -  2
12346  -  2
12387  -  3
12568  -  4
12387  -  5
12387  -  5

I have an input like shown above and I need an output also shown.

So looking at that, output should be like if the previous value is same as the present value then there shouldn't be a change in value, while if there is change in value, then the value should be incremented. First value is a default value '1'.

Is it possible to write in SQL server 2008 and above ? Are there any special functions for this ??

Can we write the same code in SSIS ? Which one will be easy to accomplish ?

Answer 1

If performance is not key constraint use this

declare @Id as int, @Rownumber as int = 0
declare @tempTable table (Id int, Rownumber int)

declare tempCur cursor for
select Id from yourtable
OPEN tempCur
FETCH NEXT FROM tempCur
INTO @Id
WHILE (@@FETCH_STATUS=0)
begin

    declare @tempId as int
    select @tempId=id from @tempTable where Rownumber=(select max(Rownumber) from @tempTable)
    if (@tempId = @Id)
        insert into @tempTable select @Id,@Rownumber
    else
    begin
        set @Rownumber = @Rownumber+1
        insert into @tempTable select @Id,@Rownumber
    end

    FETCH NEXT FROM tempCur
    INTO @Id
end
select * from @tempTable
CLOSE tempCur
DEALLOCATE tempCur

For your reference :

Answer 2

In SQL Server 08 and above you could do this easily with

SELECT Id, 
       DENSE_RANK() OVER (ORDER BY Id)
FROM Table