谢谢：我想请教一个数据拆分的问题！
我一个表中有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 
 
B    50
*/

没有主键，排序比较费劲...
[其他解释]
呵呵，版主您太给力了，谢谢！
[其他解释]

引用:

SQL code?1234567891011121314151617181920212223242526272829303132declare @T table (ID varchar(1),VAL int)insert into @Tselect 'A',55 union allselect 'A',51 union allselect 'B',40 union all……

向高手致敬！