数据分组有什么好办法？

[usun82]

sql数据库有一个表中有一个字段datekey，我想把他分成两列显示到页面，datekey中记录的数据单数放在一列双数放在一列。谢谢指教！

[叶子]

declare @datekey table ([key] int)
insert into @datekey
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11
select * from @datekey

/*
key
-----------
1
2
3
4
5
6
7
8
9
10
11
*/

declare @table1 table(id int,[key] int)
declare @table2 table(id int,[key] int)
insert into @table1
select row_number() over (order by [key] ) as row ,[key] as id from @datekey where [key]%2<>0
insert into @table2
select row_number() over (order by [key] ) as row ,[key] as id from @datekey where [key]%2=0
select a.[key] as 单数, b.[key] as 双数 from @table1 a full outer join @table2 b on a.id=b.id  
/*
单数          双数
----------- -----------
1           2
3           4
5           6
7           8
9           10
11          NULL
*/

[叶子]

可以写个存储过程，然后把结果集返回到数据层就行。
分列在存储过程里处理。

[usun82]

高！！！
但是我们现在的开发环境是SQL2000中没有row_number()函数，还有个问题，如果是下面这种情况：
ID CardID Record
1 001 7:30
2 002 7:31
3 003 7:32
4 003 7:33
5 002 7:34
6 001 7:35
生成如下结果怎么做？
ID CardID RecordA RecordB
1 001 7:30 7:35
2 002 7:31 7:34
3 003 7:32 7:33
多谢！！

[wxg22526451]

--Code
select cardid,
	recordA=max(case px when 1 then record else '' end),
	recordB=max(case px when 2 then record else '' end)
from (select *,px=(select count(1)+1 from @t  where cardid=a.cardid and record<a.record) 
from @t a) t
group by cardid
--Drop

--Result
/*
cardid recordA    recordB    
------ ---------- ---------- 
001    7:30       7:35
002    7:31       7:34
003    7:32       7:33
*/

[usun82]

wxg22526451 的方法，
declare @t table (ID int,CardID varchar(10),Record varchar(10))
insert into @t
select 1, '001','7:30' union all
select 2, '002','7:31' union all
select 3, '003','7:32' union all
select 4, '003','7:33' union all
select 5, '002','7:34' union all
select 6, '001','7:35' union all
select 7, '001','7:36' union all
select 8, '002','7:37' union all
select 9, '003','7:38' union all
select 10, '003','7:39' union all
select 11, '002','7:40' union all
select 12, '001','7:41'
select * from @t


select cardid,
recordA=max(case px when 1 then record else '' end),
recordB=max(case px when 2 then record else '' end)
from (select *,px=(select count(1)+1 from @t where cardid=a.cardid and record<a.record)
from @t a) t
group by cardid

的结果也是：

/*
cardid recordA recordB
------ ---------- ----------
001 7:30 7:35
002 7:31 7:34
003 7:32 7:33
*/

但是我想要
cardid recordA recordB
------ ---------- ----------
001 7:30 7:35
001 7:36 7:41
002 7:31 7:34
002 7:37 7:40
003 7:32 7:33
003 7:38 7:39
的结果

[01Byte空间]

顶一下。。。。

[usun82]

多谢！

[usun82]

再顶一下