如何这样整理数据？

[grjs2004]

请教各位高人：
我有个表S,其样板数据如下：
A B
1 2.5
2 6
3 7.2
4 5.1
. .
. .
. .
99 24
100 31

表T(ID,K,L)

对表S的数据，把第奇数条的B段数据填到表T的K字段，第偶数条的B字段数据填充到表T的L字段，
组合成表T的一条数据，表T最终数据如下：
ID K L
1 2.5 6
2 7.2 5.1
. . .
. . .
. . .
50 24 31

请各位高人赐教，谢谢！

[csdyyr]

DECLARE @TB TABLE([A] INT, [B] DECIMAL(10,1))
INSERT @TB 
SELECT 1, 2.5 UNION ALL 
SELECT 2, 6 UNION ALL 
SELECT 3, 7.2 UNION ALL 
SELECT 4, 5.1 UNION ALL 
SELECT 99, 24 UNION ALL 
SELECT 100, 31

SELECT (A-1)/2+1 AS ID,
	MAX(CASE WHEN (A-1)%2=0 THEN B END) AS K,
	MAX(CASE WHEN (A-1)%2=1 THEN B END) AS L
FROM @TB 
GROUP BY (A-1)/2+1
/*

ID          K            L            
----------- ------------ ------------ 
1           2.5          6.0
2           7.2          5.1
50          24.0         31.0
*/

[grjs2004]

谢谢！
如有更好的方法，则请继续！下午结贴！

[xiequan2]

DECLARE @TB TABLE([A] INT, [B] DECIMAL(10,1))
INSERT @TB 
SELECT 1, 2.5 UNION ALL 
SELECT 2, 6 UNION ALL 
SELECT 3, 7.2 UNION ALL 
SELECT 4, 5.1 UNION ALL 
SELECT 99, 24 UNION ALL 
SELECT 100, 31


;
with cte1 as
(
	select *,id=row_number() over (order by a) from @tb t
)

select  id=c2.A/2,c1.b k,c2.b l 
		from cte1 c1 left join cte1 c2 on c1.id+1=c2.id 
		where c1.id%2=1

/*
id          k                                       l
----------- --------------------------------------- ---------------------------------------
1           2.5                                     6.0
2           7.2                                     5.1
50          24.0                                    31.0

(3 行受影响)

*/

[feixianxxx]

DECLARE @TB TABLE([A] INT, [B] DECIMAL(10,1))
INSERT @TB 
SELECT 1, 2.5 UNION ALL 
SELECT 2, 6 UNION ALL 
SELECT 3, 7.2 UNION ALL 
SELECT 4, 5.1 UNION ALL 
SELECT 99, 24 UNION ALL 
SELECT 100, 31
SELECT t.A,t.k,k.L 
FROM (
select A, B as k ,0 as L  from @TB where A%2=1) T JOIN 
(select a,0 k ,B as L from @TB where A%2=0) K ON T.A=K.A-1


(6 行受影响)
A           k                                       L
----------- --------------------------------------- ---------------------------------------
1           2.5                                     6.0
3           7.2                                     5.1
99          24.0                                    31.0

(3 行受影响)

[feixianxxx]

DECLARE @TB TABLE([A] INT, [B] DECIMAL(10,1))
INSERT @TB 
SELECT 1, 2.5 UNION ALL 
SELECT 2, 6 UNION ALL 
SELECT 3, 7.2 UNION ALL 
SELECT 4, 5.1 UNION ALL 
SELECT 99, 24 UNION ALL 
SELECT 100, 31
SELECT t.A,t.k,k.L 
FROM (
select row_number() over(order by a) A, B as k ,0 as L  from @TB where A%2=1) T JOIN 
(select row_number() over(order by a) A,0 k ,B as L from @TB where A%2=0) K ON T.A=K.A
/*

(6 行受影响)
A                    k                                       L
-------------------- --------------------------------------- ---------------------------------------
1                    2.5                                     6.0
2                    7.2                                     5.1
3                    24.0                                    31.0

*/

[feixianxxx]

[Quote=引用 3 楼 xiequan2 的回复:]
SQL codeDECLARE@TBTABLE([A]INT,[B]DECIMAL(10,1))INSERT@TBSELECT1,2.5UNIONALLSELECT2,6UNIONALLSELECT3,7.2UNIONALLSELECT4,5.1UNIONALLSELECT99,24UNIONALLSELECT100,31


;with cte1as
(select*,id=row_number¡­
[/Quote]
你的ID是连续的么？

[仙道彰]

if object_id('t1') is not null
drop table t1
go
create table t1 (id int identity (1,1),col2 decimal(3,1) )
insert into t1 select 2.5
union all select 6
union all select 7.2
union all select 5.1
union all select 10.5
go

select m.a,m.k,n.l from
(select row_number() over(order by id)  as A,col2 as K,0 as L from t1 where id%2=1 ) m
join
(select row_number() over(order by id)  as A,0 as K,col2 as L from t1 where id%2=0) n
on m.a=n.a
/*a                    k                                       l
-------------------- --------------------------------------- ---------------------------------------
1                    2.5                                     2.5
2                    6.0                                     7.2
3                    5.1                                     10.5

(3 行受影响)

*/

[grjs2004]

如果表S的B列含有字符型数据呢？
比如
A B
1 2.5
2 6
3 Vc
4 SL
. .
. .
. .
99 Ap
100 31

[feixianxxx]

[Quote=引用 8 楼 grjs2004 的回复:]
如果表S的B列含有字符型数据呢？
比如
A      B
1      2.5
2      6
3      Vc
4      SL
.      .
.      .
.      .
99    Ap
100    31
[/Quote]
看4楼 5楼写的
一个是连续ID 一个是不连续的

[橡胶轮胎行业数字化高总]

如果是连续的就不用rou_number（）了，如果不是就直接除以2就行了。