sql行列转换问题。。。在线等。。
select * from #info 得到的数据是：
cardno date time
000000019 2011-04-20 06:21
000000019 2011-04-20 17:40
000000012 2011-04-20 04:39
000000012 2011-04-20 16:23
000000011 2011-04-20 04:39
000000011 2011-04-20 17:34
000000019 2011-04-22 10:23
000000019 2011-04-22 12:36
000000011 2011-04-22 08:56
000000053 2011-04-25 15:39
000000049 2011-04-25 15:33
000000049 2011-04-25 19:28
000000048 2011-04-25 15:31
000000048 2011-04-25 20:46
000000012 2011-04-26 11:53
000000012 2011-04-26 17:59
000000011 2011-04-26 13:33
000000062 2011-04-27 18:15
000000062 2011-04-27 19:07
000000061 2011-04-27 18:14
000000061 2011-04-27 21:27
000000060 2011-04-27 18:11
000000019 2011-04-22 06:21
000000019 2011-04-25 06:21
000000019 2011-04-25 17:40
000000019 2011-04-26 17:40

通过行列转换，能得到数据
cardno 2011-04-20 2011-04-22 2011-04-25 2011-04-26 2011-04-27
000000019 06:21 06:21 06:21 0 0
000000019 17:40 0 17:40 17:40 0
000000012 04:39 0 0 0 0
000000012 16:23 0 0 0 0
000000011 04:39 0 0 0 0 
000000011 17:34 0 0 0 0  
000000019 0 10:23 0 0 0
000000019 0 12:36 0 0 0
000000011 0 08:56 0 0 0
000000053 0 15:39 0 0
000000049 0 0 15:33 0 0
000000049 0 0 19:28 0 0
000000048 0 0 15:31 0 0
000000048 0 0 20:46 0 0
000000012 0 0 0 11:53 0
000000012 0 0 0 17:59 0
000000011 0 0 0 13:33 0
000000062 0 0 0 18:15
000000062 0 0 0 0 19:07
000000061 0 0 0 0 18:14
000000061 0 0 0 0 21:27
000000060 0 0 0 0 18:11

这个一个考勤数据，显示的日期，时间（最早时间和最晚时间），就是把date（日期）变成列表头，相对一个cardno有两条数据，一条是最早时间，一条是最晚时间，如果没有数据的显示0。。。
请大家帮我看看，，，在线等，解决了立马结贴！！！

------解决方案--------------------

SQL code

declare @sql varchar(max)
set @sql='select cardno '
select @sql=@sql+',max(case convert(varchar(10),date,120) when '''+ convert(varchar(10),date,120) +''' then time else null end)['+ convert(varchar(10),date,120) +']'
from(select distinct convert(varchar(10),date,120) as date from tb ) as a
select @sql=@sql+' from tb group by cardno '
select @sql=@sql+' union all select cardno'
select @sql=@sql+',min(case convert(varchar(10),date,120) when '''+ convert(varchar(10),date,120) +''' then time else null end)['+ convert(varchar(10),date,120) +']'
from(select distinct convert(varchar(10),date,120) as date from tb ) as a
select @sql=@sql+' from tb group by cardno order by cardno'
--print @sql 
exec(@sql)