数据库表结构如下
项目 参与公司 设计费用 咨询费用
A项目 A公司 200 300
A项目 B公司 300 400
B项目 B公司 400 500
C项目 C公司 500 600
... .... ... ...
数据有一万多条,公司大概有1000多个要求得到如下报表格式:
项目 A公司设计费用 A公司咨询费用 B公司设计费用 B公司咨询费用 C公司设计费用 C公司咨询费用 ...
A项目 200 300 300 400 0 0
B项目 0 0 400 500 0 0
C项目 0 0 0 0 500 600
... ... ... ... ... ... ... ...谢谢! SQL 交叉报表
项目 参与公司 设计费用 咨询费用
A项目 A公司 200 300
A项目 B公司 300 400
B项目 B公司 400 500
C项目 C公司 500 600
... .... ... ...
数据有一万多条,公司大概有1000多个要求得到如下报表格式:
项目 A公司设计费用 A公司咨询费用 B公司设计费用 B公司咨询费用 C公司设计费用 C公司咨询费用 ...
A项目 200 300 300 400 0 0
B项目 0 0 400 500 0 0
C项目 0 0 0 0 500 600
... ... ... ... ... ... ... ...谢谢! SQL 交叉报表
CREATE TABLE [dbo].[ReportsFee](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Project] [nvarchar](100) NULL,
[Company] [nvarchar](100) NULL,
[DesignFee] [decimal](18, 2) NULL,
[AskFee] [decimal](18, 2) NULL
) ON [PRIMARY]
insert into dbo.ReportsFee values('A','A',300,2000)
insert into dbo.ReportsFee values('A','B',500,100)
insert into dbo.ReportsFee values('A','A',300,120)
insert into dbo.ReportsFee values('A','C',100,210)
insert into dbo.ReportsFee values('B','D',300,250)Declare @SqlStr nvarchar(max)Set @SqlStr= 'select [Project]'
select @SqlStr+=',max(case Company when ''' + Company + ''' then [DesignFee] else 0 end) as ''' + Company + '''''_DesignFee''
,max(case Company when ''' + Company + ''' then [AskFee] else 0 end) as '''+Company+'''''_AskFee'''
from(select distinct Company from ReportsFee) as a select @SqlStr+=' from dbo.ReportsFee group by Project'
select @SqlStr
exec(@SqlStr)
if exists (select 1 from sysobjects where type = 'u' and id = object_id('csdn_20130626'))
drop table csdn_20130626
go
Create table csdn_20130626
(
xm varchar(100), --项目
cygs varchar(100), --参与公司
sjfy numeric(11,3), --设计费用
zxfy numeric(11,3) --咨询费用
)
go
truncate table csdn_20130626
go
insert into csdn_20130626 (xm,cygs,sjfy,zxfy) values('A项目','A公司',200,300);
insert into csdn_20130626 (xm,cygs,sjfy,zxfy) values('A项目','B公司',300,400);
insert into csdn_20130626 (xm,cygs,sjfy,zxfy) values('B项目','B公司',400,500);
insert into csdn_20130626 (xm,cygs,sjfy,zxfy) values('C项目','C公司',500,600);
--...
if exists(select 1 from sysobjects where type = 'p' and id = object_id('pro_csdn_20130626'))
drop procedure pro_csdn_20130626
go
Create procedure pro_csdn_20130626
as
begin
declare @xm varchar(100),
@cygs varchar(100),
@sql varchar(100),
@sjfy numeric(11,3),
@zxfy numeric(11,3)
if object_id('#temp_01') > 0
begin
drop table #temp_01;
end
Create table #temp_01(xm varchar(100))
insert into #temp_01(xm) select distinct xm from csdn_20130626 order by xm;
--
declare cur_gs cursor for
select distinct cygs from csdn_20130626 order by cygs
open cur_gs
fetch cur_gs into @cygs
while @@fetch_status = 0
begin if not exists(select 1 from syscolumns where id = object_id('#temp_01') and name = @cygs + 'sjfy')
begin
set @sql = 'alter table #temp_01 add '+ @cygs + 'sjfy numeric(11,3)'
exec(@sql)
end
if not exists(select 1 from syscolumns where id = object_id('#temp_01') and name = @cygs + 'zxfy')
begin
set @sql = 'alter table #temp_01 add '+ @cygs + 'zxfy numeric(11,3)'
exec(@sql)
end fetch cur_gs into @cygs
end
close cur_gs
deallocate cur_gs
--
declare cur_xm cursor for
select distinct xm from csdn_20130626
open cur_xm
fetch cur_xm into @xm
while @@fetch_status = 0
begin
declare cur_gs cursor for
select distinct cygs from csdn_20130626 order by cygs
open cur_gs
fetch cur_gs into @cygs
while @@fetch_status = 0
begin
set @sjfy = 0
set @zxfy = 0
select @sjfy = isnull(sjfy,0) from csdn_20130626 where xm = @xm and cygs = @cygs;
select @zxfy = isnull(zxfy,0) from csdn_20130626 where xm = @xm and cygs = @cygs;
set @sql = 'update #temp_01 set ' + @cygs + 'sjfy = ' + char(39)+ convert(varchar(100),@sjfy) + char(39) +' where xm =' + char(39)+@xm + char(39)
exec(@sql)
set @sql = 'update #temp_01 set ' + @cygs + 'zxfy = ' + char(39)+ convert(varchar(100),@zxfy) + char(39) +' where xm =' + char(39)+ @xm + char(39)
exec(@sql)
fetch cur_gs into @cygs
end
close cur_gs
deallocate cur_gs
fetch cur_xm into @xm
end
close cur_xm
deallocate cur_xm
select * from #temp_01
drop table #temp_01
endgo
exec pro_csdn_20130626
go
drop procedure pro_csdn_20130626
go
drop table csdn_20130626
go
上面的办法挺好的,代码还比较简洁,我这个也是一个办法,不过看着稍微麻烦一点:
if OBJECT_ID('t') is not null
drop table t
gocreate table t
(
项目 varchar(20),
参与公司 varchar(20),
设计费用 numeric(10,0),
咨询费用 numeric(10,0)
)
goinsert into t
select 'A项目' as a1,'A公司' as a2,200 as a3,300 as a4
union all
select 'A项目','B公司',300,400
union all
select 'B项目','B公司',400,500
union all
select 'C项目','C公司',500,600
go
declare @company_num int; --公司个数
declare @i int;
declare @sql varchar(8000);
declare @company_name varchar(20);set @company_num = (select count(distinct 参与公司) from t)
set @i = 1;
set @sql = '';while @i <= @company_num
begin
set @company_name = (
select t2.参与公司
from
(
select t1.参与公司,
row_number() over(order by 参与公司) as rownum --排序依次编号
from
(
select distinct 参与公司
from t
)t1 --先把 参与公司去重
)t2
where rownum = @i --依次取出一个公司名称
);
set @sql = @sql + ',sum(case when 参与公司 = ''' + @company_name +
''' then 设计费用 else 0 end) as ''' + @company_name + '设计费用' + '''' +
',sum(case when 参与公司 = ''' + @company_name +
''' then 咨询费用 else 0 end) as ''' + @company_name + '咨询费用' + ''''
set @i = @i + 1;
end
set @sql = 'select 项目' + @sql +
' from t ' +
'group by 项目'
select @sql exec(@sql)
sum(case when 参与公司 = 'A公司' then 设计费用 else 0 end) as 'A公司设计费用',
sum(case when 参与公司 = 'A公司' then 咨询费用 else 0 end) as 'A公司咨询费用',
sum(case when 参与公司 = 'B公司' then 设计费用 else 0 end) as 'B公司设计费用',
sum(case when 参与公司 = 'B公司' then 咨询费用 else 0 end) as 'B公司咨询费用',
sum(case when 参与公司 = 'C公司' then 设计费用 else 0 end) as 'C公司设计费用',
sum(case when 参与公司 = 'C公司' then 咨询费用 else 0 end) as 'C公司咨询费用'
from t group by 项目