declare @sql varchar(8000)
set @sql = 'select 培训地点 '
select @sql = @sql + ' , sum(case 培训专业 when ''' + 培训专业 + ''' then 1 else 0 end) [' + 培训专业 + ']'from (select distinct 培训专业 from [tbaa2]) as a
set @sql = @sql + ',count(*) as 合计 from [tbaa2] group by 培训地点'
print @sql
exec(@sql)
set @sql = 'select 培训地点 '
select @sql = @sql + ' , sum(case 培训专业 when ''' + 培训专业 + ''' then 1 else 0 end) [' + 培训专业 + ']'from (select distinct 培训专业 from [tbaa2]) as a
set @sql = @sql + ',count(*) as 合计 from [tbaa2] group by 培训地点'
print @sql
exec(@sql)
set @sql = 'select 培训地点 '
select @sql = @sql + ' , sum(case 培训专业 when ''' + 培训专业 + ''' then 1 else 0 end) [' + 培训专业 + ']'from (select distinct 培训专业 from [tbaa2]) as a
set @sql = @sql + ',count(*) as [合计] from [tbaa2] group by 培训地点'
print @sql
exec(@sql)
select @sql = @sql + ' , sum(1) [合计]'
set @sql = 'select 培训地点 '
select @sql = @sql + ' , sum(case 培训专业 when ''' + 培训专业 + ''' then 1 else 0 end) [' + 培训专业 + ']'
from (select distinct 培训专业 from [tbaa2]) as a
set @sql = @sql + ', count(1) as 合计 from [tbaa2] group by 培训地点 union select ''总计'''
select @sql = @sql + ' , sum(case 培训专业 when ''' + 培训专业 + ''' then 1 else 0 end) [' + 培训专业 + ']'
from (select distinct 培训专业 from [tbaa2]) as a
set @sql = @sql + ', count(1) as 合计 from [tbaa2]'
print @sql
exec(@sql)
-_-!,只好先把null update成'无'不知道各位大侠有没有办法
select @sql = ''
select @sql =',sum(case 培训专业 when '''+培训专业+''' then 1 else 0 end) [' + 培训专业 + ']'
from (select distinct 培训专业 from [tbaa2]) as a
set @sql = @sql+',count(*) as [合计] from [tbaa2]
set @sql = 'select 培训地点 '+@sql+' group by 培训地点 '+'union select ''总计'''+@sql
print @sql
exec(@sql)
declare @sql varchar(8000)
select @sql = ''
select @sql =',sum(case isnull(培训专业,''未知专业'') when '''+培训专业+''' then 1 else 0 end) [' + 培训专业 + ']'
from (select distinct isnull(培训专业,'未知专业') 培训专业 from [tbaa2]) as a
set @sql = @sql+',count(*) as [合计] from [tbaa2]
set @sql = 'select 培训地点 '+@sql+' group by 培训地点 '+'union select ''总计'''+@sql
print @sql
exec(@sql)