declare @sql varchar(8000)
set @sql='select wprojectid as '+'项目号'
select @sql=@sql+',max(case positionName when '''+ positionName+''' then num else 0 end)[' + positionname + ']'
from (select distinct positionName from ProjectAndPosition ) as a
set @sql=@sql+' ,sum(num) 总计 from ProjectAndPosition group by wprojectid'
exec(@sql)列总计改好了。。 最后一行加一行总计 怎么弄呢 求大神
set @sql='select wprojectid as '+'项目号'
select @sql=@sql+',max(case positionName when '''+ positionName+''' then num else 0 end)[' + positionname + ']'
from (select distinct positionName from ProjectAndPosition ) as a
set @sql=@sql+' ,sum(num) 总计 from ProjectAndPosition group by wprojectid'
exec(@sql)列总计改好了。。 最后一行加一行总计 怎么弄呢 求大神
set @sql='select isnull(wprojectid,''总计'') as '+'项目号'
select @sql=@sql+',max(case positionName when '''+ positionName+''' then num else 0 end)[' + positionname + ']'
from (select distinct positionName from ProjectAndPosition ) as a
set @sql=@sql+' ,sum(num) as 总计 from ProjectAndPosition group by wprojectid with rollup'
exec(@sql)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-27 10:10:43
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[ProjectAndPosition]
if object_id('[ProjectAndPosition]') is not null drop table [ProjectAndPosition]
go
create table [ProjectAndPosition]([wprojectid] varchar(23),[positionName] varchar(12),[num] int)
insert [ProjectAndPosition]
select 'NoProject','规划部部长',1 union all
select 'aaaaaaaaaaaa','现场经理',1 union all
select 'bbbbbbbbbbbb','项目部副部长',1 union all
select 'ccccccccccccc','项目经理',1 union all
select 'dddddddddddd','项目经理',1 union all
select 'eeeeeeeeeeee','项目经理',2 union all
select 'fffffffffffffffffffffff','项目经理助理',1
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql='select isnull(wprojectid,''总计'') as '+'项目号'
select @sql=@sql+',max(case positionName when '''+ positionName+''' then num else 0 end)[' + positionname + ']'
from (select distinct positionName from ProjectAndPosition ) as a
set @sql=@sql+' ,sum(num) as 总计 from ProjectAndPosition group by wprojectid with rollup'
exec(@sql)----------------结果----------------------------
/* 项目号 规划部部长 现场经理 项目部副部长 项目经理 项目经理助理 总计
----------------------- ----------- ----------- ----------- ----------- ----------- -----------
aaaaaaaaaaaa 0 1 0 0 0 1
bbbbbbbbbbbb 0 0 1 0 0 1
ccccccccccccc 0 0 0 1 0 1
dddddddddddd 0 0 0 1 0 1
eeeeeeeeeeee 0 0 0 2 0 2
fffffffffffffffffffffff 0 0 0 0 1 1
NoProject 1 0 0 0 0 1
总计 1 1 1 2 1 8(8 行受影响)d
*/
我还有一个小问题 这中动态的sql语句 我试了下 不能建立成视图 我想问问这种动态的sql语句是否可以往临时表里加呢?纯属好奇 问问