日期:2014-05-17 浏览次数:20574 次
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (公司 nvarchar(2),项目 nvarchar(4),金额 int)
insert into [TB]
select 'a','房费',100 union all
select 'a','房费',100 union all
select 'a','车费',100 union all
select 'b','车费',100 union all
select 'b','车费',100 union all
select 'b','门费',500
select * from [TB]
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(项目)+'=sum(case when [项目]='+quotename(项目,'''')+' then [金额] else 0 end)'
from TB group BY 项目
exec('select [公司]'+@s+',[总成绩]=sum([金额]) from TB group by [公司]')
/*
公司 车费 房费 门费 总成绩
a 100 200 0 300
b 200 0 500 700*/
USE test
GO
-->生成表tb
if object_id('tb') is not null
drop table tb
Go
Create table tb([公司] nvarchar(1),[项目] nvarchar(2),[金额] smallint)
Insert into tb
Select N'a',N'房费',100
Union all Select N'a',N'房费',100
Union all Select N'a',N'车费',100
Union all Select N'b',N'车费',100
Union all Select N'b',N'车费',100
Union all Select N'b',N'门费',500
DECLARE
@Project_pivot NVARCHAR(MAX)
,@Project_col NVARCHAR(MAX)
,@sql NVARCHAR(MAX)
SELECT @Project_pivot=ISNULL(@Project_pivot+',','')+QUOTENAME([项目]) FROM tb GROUP BY [项目]
SELECT @Project_col=ISNULL(@Project_col+',','')+'ISNULL('+QUOTENAME([项目])+',0) As '+QUOTENAME([项目]) FROM tb GROUP BY [项目]
EXEC (N'Select *,(Select Sum([金额]) From tb As o Where o.公司=t.公司) As 汇总 From (Select 公司,'+@Project_col+N' From tb As a Pivot (Sum([金额]) For [项目] In('+@Project_pivot+')) p) As t')
/*
公司 房费 车费 门费 汇总
---- ----------- ----------- ----------- -----------
a 200 100 0