行转列语气,先多谢

行转列语气,先谢谢
表内容项目不确定有多少个
表内容
公司项目金额
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
  
  
--&gt;生成表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 
 
 
*/

行转列语气,先多谢

热点推荐