create table #部门表(fdeid int,fdepartment varchar(10)); insert into #部门表 select 111,'儿科' union all select 112,'妇科' union all select 113,'手术室';create table #科目表(faccid int,fnumber int,fname varchar(10)); insert into #科目表 select 201,1001,'检查费' union all select 202,1002,'挂号费' union all select 301,1013,'餐费' union all select 203,1003,'门诊费' union all select 999,1999,'工资';create table #业务发生表 (faccid int,fdeid int,famount float); insert into #业务发生表 select 201, 112, 299 union all select 202, 112, 231 union all select 203, 111, 343 union all select 201, 111, 123 union all select 202, 112, 12 union all select 203, 113, 32 union all select 201, 113, 67 union all select 202, 111, 98 union all select 203, 112, 23 union all select 301, 113, 23.2 union all select 301, 112, 43.5 ;--按部门统计 --111 112 113 --1001 --1002 --1003 --1013 --1999declare @sel nvarchar(1000)='' ,@999 nvarchar(1000)='' ,@in nvarchar(1000)='' ,@sql nvarchar(max)=''
select @sel=(select ', ['+cast(fdeid as varchar(10))+'] as ['+cast(fdeid as varchar(10))+']' from #部门表 for xml path('')),@in=stuff((select ',['+cast(fdeid as varchar(10))+']' from #部门表 for xml path('')),1,1,''),@999=stuff((select '+isnull(['+cast(fdeid as varchar(10))+'],0)' from #部门表 for xml path('')),1,1,''); select @sql=' select fnumber '+@sel+',('+@999+')/2 as [999] from (select a.fdeid,a.famount,b.fnumber from #业务发生表 a join #科目表 b on a.faccid=b.faccid) upt pivot (sum(famount) for fdeid in ('+@in+')) pvt' exec( @sql)/* fnumber 111 112 113 999 ----------- ---------------------- ---------------------- ---------------------- ---------------------- 1001 123 299 67 244.5 1002 98 243 NULL 170.5 1003 343 23 32 199 1013 NULL 43.5 23.2 33.35 */不知道楼主是否要这个
猜测lz意思 use tempdb; /* create table 部门表 ( fdeid int not null, fdepartment nvarchar(10) not null ); insert into 部门表(fdeid,fdepartment) values (111,'儿科'), (112,'妇科'), (113,'手术室');create table 科目表 ( faccid int not null, fnumber int not null, fname nvarchar(10) not null ); insert into 科目表(faccid,fnumber,fname) values (201,1001,'检查费'), (202,1002,'挂号费'), (301,1013,'餐费'), (203,1003,'门诊费'), (999,1999,'工资');create table 业务发生表 ( faccid int not null, fdeid int not null, famount decimal(18,2) not null ); insert into 业务发生表(faccid,fdeid,famount) values (201,112,299), (202,112,231), (203,111,343), (201,111,123), (202,112,12), (203,113,32), (201,113,67), (202,111,98), (203,112,23), (301,113,23.2), (301,112,43.5); */ declare @sql varchar(8000) set @sql = 'select fname as [费用名称] ' select @sql = @sql + ' , SUM(case fdepartment when ''' + fdepartment + ''' then famount else 0 end) [' + fdepartment + ']' from (select distinct fdepartment from (select t1.faccid,t2.fdepartment,t3.fname,t1.famount from 业务发生表 as t1 join 部门表 as t2 on t1.fdeid = t2.fdeid join 科目表 as t3 on t1.faccid = t3.faccid) as t) as a set @sql = @sql + ' from (select t1.faccid,t2.fdepartment,t3.fname,t1.famount from 业务发生表 as t1 join 部门表 as t2 on t1.fdeid = t2.fdeid join 科目表 as t3 on t1.faccid = t3.faccid) as t group by fname' exec(@sql) --------儿科---妇科----手术室 餐费 0.00 43.50 23.20 挂号费 98.00 243.00 0.00 检查费 123.00 299.00 67.00 门诊费 343.00 23.00 32.00
create table #部门表(fdeid int,fdepartment varchar(10));
insert into #部门表 select 111,'儿科' union all select 112,'妇科'
union all select 113,'手术室';create table #科目表(faccid int,fnumber int,fname varchar(10));
insert into #科目表 select 201,1001,'检查费' union all select 202,1002,'挂号费'
union all select 301,1013,'餐费' union all select 203,1003,'门诊费'
union all select 999,1999,'工资';create table #业务发生表 (faccid int,fdeid int,famount float);
insert into #业务发生表
select 201, 112, 299
union all select 202, 112, 231
union all select 203, 111, 343
union all select 201, 111, 123
union all select 202, 112, 12
union all select 203, 113, 32
union all select 201, 113, 67
union all select 202, 111, 98
union all select 203, 112, 23
union all select 301, 113, 23.2
union all select 301, 112, 43.5 ;--按部门统计
--111 112 113
--1001
--1002
--1003
--1013
--1999declare @sel nvarchar(1000)=''
,@999 nvarchar(1000)=''
,@in nvarchar(1000)=''
,@sql nvarchar(max)=''
select @sel=(select ', ['+cast(fdeid as varchar(10))+'] as ['+cast(fdeid as varchar(10))+']' from #部门表 for xml path('')),@in=stuff((select ',['+cast(fdeid as varchar(10))+']' from #部门表 for xml path('')),1,1,''),@999=stuff((select '+isnull(['+cast(fdeid as varchar(10))+'],0)' from #部门表 for xml path('')),1,1,'');
select @sql=' select fnumber '+@sel+',('+@999+')/2 as [999] from (select a.fdeid,a.famount,b.fnumber from #业务发生表 a join #科目表 b on a.faccid=b.faccid) upt
pivot (sum(famount) for fdeid in ('+@in+')) pvt'
exec( @sql)/*
fnumber 111 112 113 999
----------- ---------------------- ---------------------- ---------------------- ----------------------
1001 123 299 67 244.5
1002 98 243 NULL 170.5
1003 343 23 32 199
1013 NULL 43.5 23.2 33.35
*/不知道楼主是否要这个
use tempdb;
/*
create table 部门表
(
fdeid int not null,
fdepartment nvarchar(10) not null
);
insert into 部门表(fdeid,fdepartment)
values
(111,'儿科'),
(112,'妇科'),
(113,'手术室');create table 科目表
(
faccid int not null,
fnumber int not null,
fname nvarchar(10) not null
);
insert into 科目表(faccid,fnumber,fname)
values
(201,1001,'检查费'),
(202,1002,'挂号费'),
(301,1013,'餐费'),
(203,1003,'门诊费'),
(999,1999,'工资');create table 业务发生表
(
faccid int not null,
fdeid int not null,
famount decimal(18,2) not null
);
insert into 业务发生表(faccid,fdeid,famount)
values
(201,112,299),
(202,112,231),
(203,111,343),
(201,111,123),
(202,112,12),
(203,113,32),
(201,113,67),
(202,111,98),
(203,112,23),
(301,113,23.2),
(301,112,43.5);
*/
declare @sql varchar(8000)
set @sql = 'select fname as [费用名称] '
select @sql = @sql + ' , SUM(case fdepartment when ''' + fdepartment + ''' then famount else 0 end) [' + fdepartment + ']'
from (select distinct fdepartment from (select t1.faccid,t2.fdepartment,t3.fname,t1.famount
from 业务发生表 as t1
join 部门表 as t2 on t1.fdeid = t2.fdeid
join 科目表 as t3 on t1.faccid = t3.faccid) as t) as a
set @sql = @sql + ' from (select t1.faccid,t2.fdepartment,t3.fname,t1.famount
from 业务发生表 as t1
join 部门表 as t2 on t1.fdeid = t2.fdeid
join 科目表 as t3 on t1.faccid = t3.faccid) as t group by fname'
exec(@sql)
--------儿科---妇科----手术室
餐费 0.00 43.50 23.20
挂号费 98.00 243.00 0.00
检查费 123.00 299.00 67.00
门诊费 343.00 23.00 32.00