CREATE TABLE Test
(id int,data int);

insert into test values(1,1)
insert into test values(2,1)
insert into test values(3,2)
insert into test values(4,3)

CREATE TABLE Test1
  (id int,data int);

insert into test1 values(1,1)
insert into test1 values(2,1)
insert into test1 values(3,2)
insert into test1 values(4,3)
insert into test1 values(2,1)
insert into test1 values(3,1)
insert into test1 values(4,1)
insert into test1 values(4,2)
我初始表为Test，最终我想搞成Test1，就按data判断，若data>1,则加入data值从1 到data-1的行，ID不重要，可以不变，也可以1,2,3,4,5,6,7,8按顺序增加下去，顺序也不重要，关键就是要所有的ID的data都从1开始，请帮助，谢谢

用循环可以实现的

用游标处理起来很容易

sql2005或以上版本
With T
As
(
Select * From Test  
Union All
Select B.id,T.data-1 From T Inner Join Test B
    on T.id=B.id
        Where T.data>1
)
Select * From T Order By id