--创建数据测试环境
create table #tb(type int,num int,id int)
insert into #tb
select 0,2,1
union all select 3,2,1
union all select 1,2,2
union all select 2,3,2
union all select 0,2,3
--得到你要的结果
select [0],[1],[2],[3]
from
(
select id,isnull(cast(sum(case type when 0 then num end) as varchar),'') as [0]
,isnull(cast(sum(case type when 1 then num end) as varchar),'') as [1]
,isnull(cast(sum(case type when 2 then num end) as varchar),'') as [2]
,isnull(cast(sum(case type when 3 then num end) as varchar),'') as [3]
from #tb
group by id
) aa
as
Begin
--if @ncBranchNo='#####'
--select @ncBranchNo=''
SET NOCOUNT ON
create table #tSubjectDetail
(
ncBranchNo nvarchar(55)
,ncSubjectNo nvarchar(100)
,mSubjectData money
)
insert into #tSubjectDetail
select c.ncBranchNo+'_'+a.nvcBranchName,c.ncSubjectNo+'_'+b.nvcSubjectName,c.mSubjectData
from tBranch a,tSubjectType b, fnStatisticwhz(@ncBranchNo,@start_date,@end_date,@tiTypeNo,@ncSubjectNo) c
where a.ncBranchNo=c.ncBranchNo and b.ncSubjectNo=c.ncSubjectNo
End
If (select count(*) from #tSubjectDetail)>0
Begin
SET NOCOUNT ON
declare @sql varchar(2000),@fdname varchar(100)
select @sql=''
declare #aa SCROLL cursor for
select distinct ncSubjectNo from #tSubjectDetail
open #aa
fetch next from #aa into @fdname
while @@fetch_status=0
begin
select @sql=@sql+',sum(case when ncSubjectNo=''' +@fdname+''' then mSubjectdata else 0 end) as ['+@fdname+']'
fetch next from #aa into @fdname
end
close #aa
deallocate #aa
select @sql='select ncBranchNo 局所名称,'+right(@sql,len(@sql)-1)+'from #tSubjectDetail group by ncBranchNo'
print @sql
exec(@sql)
drop table #tSubjectDetail
End
Else
Begin
SET NOCOUNT ON
select @sql='select ncBranchNo 局所名称 from #tSubjectDetail'
print @sql
declare @sql varchar(8000)
select @sql=''
select @sql=@sql+',sum(case when kmdh='''+kmdh+''' then kmcj else 0 end) as ['+kmmc+']' from #km
exec ('select identity(int,1,1) as [sequence] ,b.st_name'+@sql+', sum(kmcj) as total into ##t from #kmcj a inner join #student b on a.st_id=b.st_id group by b.st_name order by total desc')
select * from ##t
go
drop table #kmcj
drop table #student
drop table #km
drop table ##t
declare @sql varchar(8000)
set @sql = 'select num'
select @sql = @sql + ',sum(case type when '''+cast(type as varchar(10))+''' then id end) ['+cast(type as varchar(10))+']'
from (select distinct type from 有一表) as a
select @sql = @sql+' from 有一表 group by num'
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as a
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as a