求一查询
我有一张表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
(idInt,
nameNvarchar(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
1011122007-04-05
914152007-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 '
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(id int,name varchar(10),[count] int,time varchar(10))
insert into tb(id,name,[count],time) values(1, '刘 ', 10, '2007-04-05 ')
insert into tb(id,name,[count],time) values(2, '王 ', 11, '2007-04-05 ')
insert into tb(id,name,[count],time) values(3, '张 ', 12, '2007-04-05 ')
insert into tb(id,name,[count],time) values(4, '刘 ', 9 , '2007-04-06 ')
insert into tb(id,name,[count],time) values(5, '王 ', 14, '2007-04-06 ')
insert into tb(id,name,[count],time) values(6, '张 ', 15, '2007-04-06 ')
go
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + ' max(case name when ' ' ' + name + ' ' ' then [count] else ' ' ' ' end) [ ' + name + '] , '
from (select distinct name from tb) as a
set @sql = @sql + ' time from tb group by time '
exec(@sql)
drop table tb
/*
刘 王 张 time
----------- ----------- ----------- ----------
10 11 12 2007-04-05
9 14 15 2007-04-06
*/
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(id int,name varchar(10),[count] int,time varchar(10))
insert into tb(id,name,[count],time) values(1, '刘 ', 10, '2007-04-05 ')
insert into tb(id,name,[count],time) values(2, '王 ', 11, '2007-04-05 ')
insert into tb(id,name,[count],time) values(3, '张 ', 12, '2007-04-05 ')
insert into tb(id,name,[count],time) values(4, '刘 ', 9 , '2007-04-06 ')
insert into tb(id,name,[count],time) values(5, '王 ', 14, '2007-04-06 ')
insert into tb(id,name,[count],time) values(6, '张 ', 15, '2007-04-06 ')
insert into tb(id,name,[count],time) values(7, '金 ', 20, '2007-04-05 ')
insert into tb(id,name,[count],time) values(8, '金 ', 21, '2007-04-06 ')
insert into tb(id,name,[count],time) values(9, '完颜 ', 15, '2007-04-05 ')
go
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + ' max(case name when ' ' ' + name + ' ' ' then [count] else 0 end) [ ' + name + '] , '
from (select distinct name from tb) as a
set @sql = @sql + ' time from tb group by time '
exec(@sql)
drop table tb
/*
金 刘 完颜 王 张 time
----------- ----------- ----------- ----------- ----------- ----------
20 10 15 11 12 2007-04-05
21 9 0 14 15 2007-04-06
*/
[解决办法]
用动态SQL语句，都上都给出来了。
动态sql语句基本语法
1 :普通SQL语句可以用Exec执行
eg: Select * from tableName
Exec( 'select * from tableName ')
Exec sp_executesql N 'select * from tableName ' -- 请注意字符串前一定要加N
2:字段名，表名，数据库名之类作为变量时，必须用动态SQL
eg:
declare @fname varchar(20)
set @fname = 'FiledName '
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。
Exec( 'select ' + @fname + ' from tableName ') -- 请注意 加号前后的 单引号的边上加空格
当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName ' --设置字段名
declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName '
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错
declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName '
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确
3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls= 'select count(*) from tableName '
exec(@sqls)
--如何将exec执行结果放入变量中？
declare @num int,
@sqls nvarchar(4000)
set @sqls= 'select @a=count(*) from tableName '
exec sp_executesql @sqls,N '@a int output ',@num output
select @num
