When you use variables in update statement and update the columns with the values of variables
which keep on changing for each row, you can simulate many things that are done using Loop or a Cursor

declare @temp table 
(
product_name varchar(100),
Value int,
sequence int
)

insert into @temp values('product1' , 25 , 0)
insert into @temp values('product2' , 25 , 0)
insert into @temp values('product3' , 100 , 0)
insert into @temp values('product4' , 100 , 0)
insert into @temp values('product5' , 100 , 0)
insert into @temp values('product6' , 25 , 0)
insert into @temp values('product7' , 25 , 0)
insert into @temp values('product8' , 26 , 0) 

declare @value int,@sequence int, @runningtotal int,@product_name varchar(100)

--Serial No for all values 

set @sequence=0

update @temp
set @sequence=sequence =@sequence+1
select * from @temp

--Serial No for each value

set @sequence=0

update @temp
set sequence=@sequence ,
@sequence=case when @value=value then @sequence else @sequence+1 end,@value=value 

select * from @temp 

--Reset Serial No in each value 

set @sequence=0

update @temp
set sequence=@sequence ,
@sequence=case when @value=value then @sequence+1 else 1 end,@value=value 

select * from @temp

--Running total

set @sequence=0

update @temp
set @sequence=sequence =@sequence+value 
select * from @temp

--Generate data in csv format

GO

 

declare @value int,@sequence varchar(100), @product_name varchar(100)

declare @temp table 
(
product_name varchar(100),
Value int,
sequence varchar(100)
)

insert into @temp values('product1' , 25 , 0)
insert into @temp values('product2' , 25 , 0)
insert into @temp values('product3' , 100 , 0)
insert into @temp values('product4' , 100 , 0)
insert into @temp values('product5' , 100 , 0)
insert into @temp values('product6' , 26 , 0) 
 

set @product_name=''

update @temp
set sequence=@product_name ,
@product_name=case when @value=value then @product_name+','+product_name else product_name end,@value=value 
select value,max(sequence) as sequence from @temp
group by value

The term "Quirky update" is referred in these articles
http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/