怎么样把一列的数据平均成两列
怎么样把一列的数据平均成两列
例如把
序号 车号
1 n1
2 n2
3 n3
4 n4
变成
序号 车号 序号 车号
1 n1 3 n3
2 n2 4 n4
[解决办法]
create table t (id int, name varchar(2))
--drop table t
insert into t
select 1, 'n1 ' union
select 2, 'n2 ' union
select 3, 'n3 ' union
select 4, 'n4 '
select a.id, a.name, b.id, b.name
from (
select (select count(*) from t a where t.id> a.id) cnt,*
from t
where (select count(*) from t a where t.id> a.id) < (select count(*) from t)/2
) a
left join (
select ((select count(*) from t a where t.id> a.id)-(select count(*) from t)/2) cnt,*
from t
where (select count(*) from t a where t.id> a.id) > = (select count(*) from t)/2
) b
on a.cnt=b.cnt
where a.cnt < (select count(*) from t)/2
--------结果----------
id name id name
----------- ---- ----------- ----
1 n1 3 n3
2 n2 4 n4
（所影响的行数为 2 行）
[解决办法]
select * from (select sid=id/cnt,ssid=id mod cnt,id,ch from table) a join
(select sid=id/cnt,ssid=id mod cnt,id,ch from table) b on (b.sid> a.sid and b.ssid=a.ssid)
其中cnt为表中id值的一半
[解决办法]
create table t (id int, name varchar(2))
insert into t
select 1, 'n1 ' union
select 2, 'n2 ' union
select 3, 'n3 ' union
select 4, 'n4 '
GO
Select
Max(Case (id - 1) / 2 When 0 Then id Else Null End) As 序号1,
Max(Case (id - 1) / 2 When 0 Then name Else Null End) As 车号1,
Max(Case (id - 1) / 2 When 1 Then id Else Null End) As 序号2,
Max(Case (id - 1) / 2 When 1 Then name Else Null End) As 车号
From
t
Group By
id % 2
Order By
序号1
GO
Drop Table t
/*
序号1车号1序号2车号2
1n13n3
2n24n4
*/
