求一查询
我有一张表A
        id     name     count     time
        1         刘       10           2007-04-05
        2         王       11           2007-04-05
        3         张       12           2007-04-05
        4         刘       9             2007-04-06
        5         王       14           2007-04-06
        6         张       15           2007-04-06
//就是每天都有这三个人（人可以更多）的记录

//我想得到这样的结果集：
    刘       王         张       time
    10       11         12       2007-04-05
    9         14         15       2007-04-06

请问应该怎么写sql

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

Declare @S Nvarchar(4000)
Select @S = ' '
Select @S = @S + ', SUM(Case name When ' ' ' + name + ' ' ' Then [count] Else 0 End) As ' + name
From A Group By name
Select @S = Stuff(@S, 1, 1, ' ') + ' [time] From A Group By [time] '
EXEC(@S)
------解决方案--------------------
有bug，修改下

Declare @S Nvarchar(4000)
Select @S = ' '
Select @S = @S + ', SUM(Case name When N ' ' ' + name + ' ' ' Then [count] Else 0 End) As ' + name
From A Group By name Order By Min(id)
Select @S = ' Select ' + Stuff(@S, 1, 1, ' ') + ' ,[time] From A Group By [time] '
EXEC(@S)
------解决方案--------------------
Create Table A
(id Int,
name Nvarchar(10),
[count] Int,
[time] Varchar(10))
Insert A Select 1, N '刘 ', 10, '2007-04-05 '
Union All Select 2, N '王 ', 11, '2007-04-05 '
Union All Select 3, N '张 ', 12, '2007-04-05 '
Union All Select 4, N '刘 ', 9, '2007-04-06 '
Union All Select 5, N '王 ', 14, '2007-04-06 '
Union All Select 6, N '张 ', 15, '2007-04-06 '
GO
Declare @S Nvarchar(4000)
Select @S = ' '
Select @S = @S + ', SUM(Case name When N ' ' ' + name + ' ' ' Then [count] Else 0 End) As ' + name
From A Group By name Order By Min(id)
Select @S = ' Select ' + Stuff(@S, 1, 1, ' ') + ' ,[time] From A Group By [time] '
EXEC(@S)
GO
Drop Table A
--Result
/*
刘 王 张 time
10 11 12 2007-04-05
9 14 15 2007-04-06
*/
------解决方案--------------------
--------------try
declare @Sql varchar(8000)
select @sql = 'select '
select @sql =@sql + 'sum (case when name = ' ' ' +name + ' ' ' then count else 0 end ) as ' ' '+ name + ' ', ' from a group by name
select @sql =@sql + ' time from a group by time '