--动态的declare @tt nvarchar(4000) select @tt=isnull(@tt+',','')+quotename([shoppName]) from shopp group by [shoppName] exec('select [shoppID],'+@tt+',[总价格] from (select *,[总价格]=sum([price])over(partition by[shoppID]) from shopp)a pivot(max([price]) for [shoppName]in ('+@tt+'))b')--结果 shoppID 饼干 凤爪 瓜子 面包 总价格 0123 12 44 32 10 108 0124 23 8 15 20 66 ------字段替换一下 看是不是你要的 结果
use Tempdb go --> -->
if not object_id(N'T') is null drop table T Go Create table T([月份] nvarchar(3),[期初总人数] int,[离职总人数] int,[入职总人数] int,[期末总人数] int) Insert T select N'一月份',47,6,3,50 union all select N'二月份',0,1,48,47 union all select N'三月份',50,0,3,53 Go declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000) select @s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)', @s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'='''+case when @s2 is not null then 'union all select' else ' select ' end+' [人数统计]='''+quotename(Name,'''')+'''''', @s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+'+'',''+quotename([月份])+''=''+quotename('+quotename(Name)+','''''''') from T ', @s4=isnull(@s4+'+','')+'@'+rtrim(Colid) from syscolumns where id=object_id('T') and Name not in('月份') --print @s+' '+@s2+' '+@s3+' exec('+@s4+')' 显示执行语句 exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')') /* 人数统计 一月份 二月份 三月份 离职总人数 6 1 0 期初总人数 47 0 50 期末总人数 50 47 53 入职总人数 3 48 3 */
http://www.cnblogs.com/worfdream/articles/2409162.html
select @tt=isnull(@tt+',','')+quotename([shoppName])
from shopp group by [shoppName]
exec('select [shoppID],'+@tt+',[总价格] from (select *,[总价格]=sum([price])over(partition by[shoppID])
from shopp)a pivot(max([price]) for [shoppName]in ('+@tt+'))b')--结果
shoppID 饼干 凤爪 瓜子 面包 总价格
0123 12 44 32 10 108
0124 23 8 15 20 66
------字段替换一下 看是不是你要的 结果
go
--> -->
if not object_id(N'T') is null
drop table T
Go
Create table T([月份] nvarchar(3),[期初总人数] int,[离职总人数] int,[入职总人数] int,[期末总人数] int)
Insert T
select N'一月份',47,6,3,50 union all
select N'二月份',0,1,48,47 union all
select N'三月份',50,0,3,53
Go
declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000)
select
@s=isnull(@s+',','declare ')+'@'+rtrim(Colid)+' nvarchar(4000)',
@s2=isnull(@s2+',','select ')+'@'+rtrim(Colid)+'='''+case when @s2 is not null then 'union all select' else ' select ' end+' [人数统计]='''+quotename(Name,'''')+'''''',
@s3=isnull(@s3,'')+'select @'+rtrim(Colid)+'=@'+rtrim(Colid)+'+'',''+quotename([月份])+''=''+quotename('+quotename(Name)+','''''''') from T ',
@s4=isnull(@s4+'+','')+'@'+rtrim(Colid)
from
syscolumns
where
id=object_id('T') and Name not in('月份')
--print @s+' '+@s2+' '+@s3+' exec('+@s4+')' 显示执行语句
exec(@s+' '+@s2+' '+@s3+' exec('+@s4+')') /*
人数统计 一月份 二月份 三月份
离职总人数 6 1 0
期初总人数 47 0 50
期末总人数 50 47 53
入职总人数 3 48 3
*/
http://blog.csdn.net/roy_88/article/details/2715856
if object_id('Proc_empSum2')is not null
drop proc Proc_empSum2
go
create proc Proc_empSum2
as
declare @sql1 varchar(4000),@sql2 varchar(4000),@sql3 varchar(4000),@sql4 varchar(4000)
select @sql1='',@sql2='',@sql3='',@sql4=''
select
@sql1=@sql1+',['+月份+']='''+[月初人数]+'''',
@sql2=@sql2+','''+[月中入职]+'''',
@sql3=@sql3+','''+[月中离职]+'''',
@sql4=@sql4+','''+[月末人数]+''''
from #tb_test1
exec ('select 月份=''月初人数'''+@sql1+' union all select ''月中入职'''+@sql2+' union all select ''月中离职'''+@sql3+' union all select ''月末人数'''+@sql4)
go
exec Proc_empSum2
--效果!下图月初人数 52 53 57 0 0 0
月末人数 51 52 53 57 0 0
月中离职 1 1 4 1 0 0
月中入职 0 0 0 58 0 0