在论坛中出现的比较难的sql问题：42(动态行转列 考勤时间动态列)

最近，在论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。

所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。

求考勤查询语句

http://bbs.csdn.net/topics/390709333

sql server 2000，考勤表：
carddate                empno   cardtime2
2014-02-12 00:00:00 A012152 13:23:16
2014-02-12 00:00:00 A012152 12:04:01
2014-02-12 00:00:00 A012152 17:35:21
2014-02-12 00:00:00 A012152 07:45:56
2014-02-11 00:00:00 A012152 13:19:18
2014-02-11 00:00:00 A012152 12:03:26
2014-02-11 00:00:00 A012152 07:44:19
2014-02-11 00:00:00 A012152 17:35:22
2014-02-10 00:00:00 A012152 17:34:14
2014-02-10 00:00:00 A012152 13:22:35
2014-02-10 00:00:00 A012152 12:02:54
2014-02-10 00:00:00 A012152 07:44:33
需要数据
carddate                empno   cardtime1 cardtime1 cardtime1 cardtime1
2014-02-12 00:00:00 A012152 13:23:16  12:04:01  17:35:21  07:45:56
2014-02-11 00:00:00 A012152 13:19:18  12:03:26  07:44:19  17:35:22
2014-02-10 00:00:00 A012152 17:34:14  13:22:35  12:02:54  07:44:33

我的方法：

if OBJECT_ID('tempdb..#temp') is not null
   drop table #temp
   
if OBJECT_ID('tempdb..#temp1') is not null
   drop table #temp1
      
select *,identity(int,1,1) as id into #temp
from 考勤表

select *,
       (select COUNT(*) from #temp b 
        where a.empno = b.empno and a.carddate = b.carddate and a.id >= b.id) rownum
       into #temp1
from #temp a


declare @sql nvarchar(max);
set @sql = '';

select 
       @sql = @sql + ',max(case when rownum = '+cast(rownum as varchar)+' then cardtime2 else null end) as cardtime'+ cast(rownum as varchar)
from #temp1
group by rownum


select @sql = 'select carddate,empno' + @sql + 
              ' from #temp1' +
              ' group by carddate,empno
                order by empno,carddate desc'
              
--select @sql

exec(@sql)
/*
carddate	empno	cardtime1	cardtime2	cardtime3	cardtime4
2014-02-12 00:00:00.000	A012152	13:23:16	12:04:01	17:35:21	07:45:56
2014-02-11 00:00:00.000	A012152	13:19:18	12:03:26	07:44:19	17:35:22
2014-02-10 00:00:00.000	A012152	17:34:14	13:22:35	12:02:54	07:44:33
*/