Clever Geek Handbook

SQL Server or SSIS code to grow

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 as shown above and I need the output shown also.

Therefore, looking at this, the output should look as if the previous value was the same as the current value, then there should be no change in the value, and if there is a change in the value, then the value should be increased. The first value is the default '1'.

Can I write in SQL Server 2008 and up? Are there any special functions for this?

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

+3


source to share


2 answers


If performance is not a key limitation then 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 reference:

enter image description here

+3


source


In SQL Server 08 and up, you can do this easily with



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


source






More articles:


All Articles