select 退休=sum(case when 病人类型=N'退休' then 报销金额 else 0 end), 在职=sum(case when 病人类型=N'在职' then 报销金额 else 0 end), 内退=sum(case when 病人类型=N'内退' then 报销金额 else 0 end) from TB
if object_id('[tb]') is not null drop table [tb] go create table [tb]([个人编码] int,[姓名] varchar(4),[结算时间] datetime,[总金额] int,[报销金额] int,[个人自付金额] int,[病人类型] varchar(4)) insert [tb] select 122,'张三','2009-7-1',1000,600,400,'退休' union all select 133,'李四','2009-7-1',1500,1000,500,'在职' union all select 144,'王五','2009-7-1',2000,1500,500,'内退'
--->查询 select distinct '报销金额' as [病人类型], (select sum(报销金额)from tb where [病人类型]='退休') as [退休], (select sum(报销金额)from tb where [病人类型]='在职') as [在职], (select sum(报销金额)from tb where [病人类型]='内退') as [内退] from tb/** 病人类型 退休 在职 内退 -------- ----------- ----------- ----------- 报销金额 600 1000 1500(所影响的行数为 1 行) **/
create table tb(个人编码 int,姓名 varchar(8),结算时间 datetime,总金额 int,报销金额 int,个人自付金额 int,病人类型 varchar(4)) go insert into tb select 122,'张三','2009-7-1',1000,600,400,'退休' insert into tb select 133,'李四','2009-7-1',1500,1000,500,'在职' insert into tb select 144,'王五','2009-7-1',2000,1500,500,'内退'go select * from tbdeclare @sql varchar(8000) set @sql='' select @sql=@sql+'sum(case 病人类型 when '''+病人类型+''' then 报销金额 else 0 end) as '+病人类型+',' from (select 病人类型 from tb group by 病人类型) t set @sql='select '+left(@sql,len(@sql)-1)+' from tb' exec(@sql)go drop table tb; go 个人编码 姓名 结算时间 总金额 报销金额 个人自付金额 病人类型 ----------- -------- ------------------------------------------------------ ----------- ----------- ----------- ---- 122 张三 2009-07-01 00:00:00.000 1000 600 400 退休 133 李四 2009-07-01 00:00:00.000 1500 1000 500 在职 144 王五 2009-07-01 00:00:00.000 2000 1500 500 内退(所影响的行数为 3 行)内退 退休 在职 ----------- ----------- ----------- 1500 600 1000(所影响的行数为 1 行)
go
create table [tb]([个人编码] int,[姓名] varchar(4),[结算时间] datetime,[总金额] int,[报销金额] int,[个人自付金额] int,[病人类型] varchar(4))
insert [tb]
select 122,'张三','2009-7-1',1000,600,400,'退休' union all
select 133,'李四','2009-7-1',1500,1000,500,'在职' union all
select 144,'王五','2009-7-1',2000,1500,500,'内退'
--->查询
select
distinct
'报销金额' as [病人类型],
(select sum(报销金额)from tb where [病人类型]='退休') as [退休],
(select sum(报销金额)from tb where [病人类型]='在职') as [在职],
(select sum(报销金额)from tb where [病人类型]='内退') as [内退]
from
tb/**
病人类型 退休 在职 内退
-------- ----------- ----------- -----------
报销金额 600 1000 1500(所影响的行数为 1 行)
**/
go
insert into tb select 122,'张三','2009-7-1',1000,600,400,'退休'
insert into tb select 133,'李四','2009-7-1',1500,1000,500,'在职'
insert into tb select 144,'王五','2009-7-1',2000,1500,500,'内退'go
select * from tbdeclare @sql varchar(8000)
set @sql=''
select @sql=@sql+'sum(case 病人类型 when '''+病人类型+''' then 报销金额 else 0 end) as '+病人类型+',' from (select 病人类型 from tb group by 病人类型) t
set @sql='select '+left(@sql,len(@sql)-1)+' from tb'
exec(@sql)go
drop table tb;
go
个人编码 姓名 结算时间 总金额 报销金额 个人自付金额 病人类型
----------- -------- ------------------------------------------------------ ----------- ----------- ----------- ----
122 张三 2009-07-01 00:00:00.000 1000 600 400 退休
133 李四 2009-07-01 00:00:00.000 1500 1000 500 在职
144 王五 2009-07-01 00:00:00.000 2000 1500 500 内退(所影响的行数为 3 行)内退 退休 在职
----------- ----------- -----------
1500 600 1000(所影响的行数为 1 行)