行转列语气,先谢谢
表内容 项目不确定有多少个
表内容
公司 项目 金额
a 房费 100
a 房费 100
a 车费 100
b 车费 100
b 车费 100
b 门费 500
生成内容
公司 房费 车费 门费 汇总
a 200 100 0 300
b 0 200 500 700
注意:项目不确定有多少个
[解决办法]
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 [公司]')
/*
公司车费房费门费总成绩
a1002000300
b2000500700*/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 300
b 0 200 500 700
*/