我一个表中有2个字段，数据如下
ID VAL
A 55
A 51
B 40
B 50
B 107
现在我想以50为界，超过50的，我就折成两条数据放到另外一个表中去，比如上面的结果，我另外一个表中的结果应该是：
ID VAL
A 50
A 5
A 50
A 1
B 40
B 50
B 50
B 50
B 7
不知道可不可以实现，请指教，谢谢！
------最佳解决方案--------------------
如果有个唯一标识，排序就容易多了。
declare @T table (rowid int identity(1,1),ID varchar(1),VAL int)
insert into @T
select 'A',55 union all
select 'A',51 union all
select 'B',40 union all
select 'B',50 union all
select 'B',107
;with maco as
(
select rowid,ID,VAL,ceiling(val/50.0) as c1,val%50 as c2 from @T
)
select a.rowid,a.ID,50 as VAL from maco a left join master..spt_values b
on 1=1
where a.c1=b.number and b.type='p' and b.number>0
union all
select rowid,ID,c2 from maco where c2<>0
order by rowid,val desc
/*
rowid ID VAL
----------- ---- -----------
1 A 50
1 A 5
2 A 50
2 A 1
3 B 50
3 B 40
4 B 50
5 B 50
5 B 7
(9 row(s) affected)
*/
------其他解决方案--------------------
最好是能用SQL 查询语句实现！
------其他解决方案--------------------
declare @T table (ID varchar(1),VAL int)
insert into @T
select 'A',55 union all
select 'A',51 union all
select 'B',40 union all
select 'B',50 union all
select 'B',107
;with maco as
(
select ID,VAL,ceiling(val/50.0) as c1,val%50 as c2 from @T
)
select a.ID,50 as VAL from maco a left join master..spt_values b
on 1=1
where a.c1=b.number and b.type='p' and b.number>0
union all
select ID,c2 from maco where c2<>0
order by ID
/*
ID VAL
---- -----------
A 50
A 50
A 5
A 1
B 40
B 7
B 50
B 50