求大侠解决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)
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
end
go
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
go
create table t
(
项目 varchar(20),
参与公司 varchar(20),
设计费用 numeric(10,0),
咨询费用 numeric(10,0)
)
go
insert 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)
