一个交叉表问题，列名是时间格式的！
原始表：
Name Hdate Hours
A 2007-2-1 0:0:0 8
B 2007-2-7 0:0:0 8
C 2007-2-1 0:0:0 8
A 2007-4-1 0:0:0 8
B 2007-5-1 0:0:0 8
其中：Name表示员工姓名，Hdate表示请假日期，Hours表示请假时间.
目标表：
Name 2007-2-1 2007-2-7 2007-4-1 2007-5-1
A 8 0 8 0
B 0 8 0 8
C 8 0 0 0
请教大家怎样写这个SQL语句？？谢谢！
[解决办法]
借用咖啡熊的數據
create table test(Name varchar(10),Hdate DateTime,Hours int)
insert test select 'A ', '2007-2-1 0:0:0 ',8
union all select 'B ', '2007-2-7 0:0:0 ',8
union all select 'C ', '2007-2-1 0:0:0 ',8
union all select 'A ', '2007-4-1 0:0:0 ',8
union all select 'B ', '2007-5-1 0:0:0 ',8
Declare @S Varchar(8000)
Select @S = ' Select Name '
Select @S = @S + ', SUM(Case Convert(Varchar(10), Hdate, 120) When ' ' ' + Hdate + ' ' ' Then Hours Else 0 End) As [ ' + Hdate + '] '
From (Select Distinct Convert(Varchar(10), Hdate, 120) As Hdate From test) A Order By Hdate
Select @S = @S + ' From test Group By Name '
EXEC(@S)
Drop Table test
--Result
/*
Name2007-02-012007-02-072007-04-012007-05-01
A8080
B0808
C8000
*/
