34,594
社区成员
发帖
与我相关
我的任务
分享
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 状态 from tb group by 状态
set @sql = '[' + @sql + ']'
exec ('select * from tb a pivot (max(数目) for 状态 in (' + @sql + ')) b')
create table #tb
(类型 nvarchar(50), 状态 nvarchar(50), 数目 int)
insert #tb
select '退预存款','流程结束',2 union all
select '退预存款','营业厅稽核',5 union all
select '退预存款','未开始',10 union all
select '退费登记明细','未开始',1663 union all
select '业务回退明细','未开始',269
declare @sql as nvarchar(2000)
declare @sql2 as nvarchar(2000)
set @sql=''
set @sql2=''
select @sql=@sql+','+状态,@sql2=@sql2+',isnull('+状态+',0) as '+状态 from (select distinct 状态 from #tb) as t
set @sql='select 类型'+@sql2+' from #tb pivot(max(数目) for 状态 in('+stuff(@sql,1,1,'')+')) a'
exec(@sql)
create table tb(类型 varchar(20),状态 varchar(20),数目 int)
insert into tb
select '退预存款','流程结束',2 union all
select '退预存款','营业厅稽核',5 union all
select '退预存款','未开始',10 union all
select '退费登记明细','未开始',1663 union all
select '业务回退明细','未开始',269
go
declare @sql varchar(4000)
declare @str varchar(4000)
declare @stg varchar(1000)
set @sql = N'select [类型]'
select @sql = @sql + N',sum(case [状态] when ''' + [状态] + ''' then [数目] else 0 end)[' + [状态] + ']'
from (select distinct [状态] from tb)t
select @sql = @sql + N' from tb group by [类型]'
exec(@sql)
set @str = ''
set @stg = ''
select @str = @str + ',[' + [状态] + ']' from (select distinct [状态] from tb)t
select @stg = @stg + ',isnull([' + [状态] + '],0) [' + [状态] + ']' from (select distinct [状态] from tb)t
select @str = N'select [类型],' + stuff(@stg,1,1,'')
+ N' from tb pivot (sum([数目]) for [状态] in ('+stuff(@str,1,1,'')+'))pt'
exec(@str)
drop table tb
/********************
类型 流程结束 未开始 营业厅稽核
-------------------- ----------- ----------- -----------
退费登记明细 0 1663 0
退预存款 2 10 5
业务回退明细 0 269 0
(3 行受影响)
类型 流程结束 未开始 营业厅稽核
-------------------- ----------- ----------- -----------
退费登记明细 0 1663 0
退预存款 2 10 5
业务回退明细 0 269 0
(3 行受影响)
create table tb(类型 varchar(20),状态 varchar(20),数目 int)
insert into tb
select '退预存款','流程结束',2 union all
select '退预存款','营业厅稽核',5 union all
select '退预存款','未开始',10 union all
select '退费登记明细','未开始',1663 union all
select '业务回退明细','未开始',269
--select * from tb;
select 类型, SUM(case 状态 when '流程结束' then 数目 else 0 end ) as 流程结束
,SUM(case 状态 when '营业厅稽核' then 数目 else 0 end) 营业厅稽核
,SUM(case 状态 when '未开始' then 数目 else 0 end) 未开始
from tb group by 类型
create table tb(类型 varchar(20),状态 varchar(20),数目 int)
insert into tb
select '退预存款','流程结束',2 union all
select '退预存款','营业厅稽核',5 union all
select '退预存款','未开始',10 union all
select '退费登记明细','未开始',1663 union all
select '业务回退明细','未开始',269
go
declare @sql varchar(4000)
declare @str varchar(4000)
set @sql = N'select [类型]'
select @sql = @sql + N',sum(case [状态] when ''' + [状态] + ''' then [数目] else 0 end)[' + [状态] + ']'
from (select distinct [状态] from tb)t
select @sql = @sql + N' from tb group by [类型]'
exec(@sql)
set @str = ''
select @str = @str + ',[' + [状态] + ']' from (select distinct [状态] from tb)t
select @str = N'select [类型],' + stuff(@str,1,1,'')
+ N' from tb pivot (sum([数目]) for [状态] in ('+stuff(@str,1,1,'')+'))pt'
exec(@str)
drop table tb
/*********
类型 流程结束 未开始 营业厅稽核
-------------------- ----------- ----------- -----------
退费登记明细 0 1663 0
退预存款 2 10 5
业务回退明细 0 269 0
(3 行受影响)
类型 流程结束 未开始 营业厅稽核
-------------------- ----------- ----------- -----------
退费登记明细 NULL 1663 NULL
退预存款 2 10 5
业务回退明细 NULL 269 NULL
(3 行受影响)
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 状态 from tb group by 状态
set @sql = '[' + @sql + ']'
exec ('select * from tb a pivot (max(数目) for 状态 in (' + @sql + ')) b')
create table tb(类型 varchar(20),状态 varchar(20),数目 int)
insert into tb
select '退预存款','流程结束',2 union all
select '退预存款','营业厅稽核',5 union all
select '退预存款','未开始',10 union all
select '退费登记明细','未开始',1663 union all
select '业务回退明细','未开始',269
go
declare @sql varchar(4000)
set @sql = N'select [类型]'
select @sql = @sql + N',sum(case [状态] when ''' + [状态] + ''' then [数目] else 0 end)[' + [状态] + ']'
from (select distinct [状态] from tb)t
select @sql = @sql + N' from tb group by [类型]'
exec(@sql)
drop table tb
/**********
类型 流程结束 未开始 营业厅稽核
-------------------- ----------- ----------- -----------
退费登记明细 0 1663 0
退预存款 2 10 5
业务回退明细 0 269 0
(3 行受影响)
declare @sql varchar(4000)
set @sql = N'select [类型]'
select @sql = @sql + N',sum(case [状态] when ''' + [状态] + ''' then [数目] else 0 end)[' + [状态] + ']'
from (select distinct [状态] from tb)t
select @sql = @sql + N' from tb group by [类型]'
exec(@sql)