declare @sql varchar(3000)
set @sql='select id'
select @sql=@sql+',max(case month when '''+month+''' then monthexamin end)['+month+']'
from (select distinct month from 表名)a
set @sql=@sql+',totalA=sum(case monthexamin when ''A'' then 1 else 0 end)
,totalB=sum(case monthexamin when ''B'' then 1 else 0 end)
set @sql=@sql+' from 表名 group by id'
exec(@sql)
set @sql='select id'
select @sql=@sql+',max(case month when '''+month+''' then monthexamin end)['+month+']'
from (select distinct month from 表名)a
set @sql=@sql+',totalA=sum(case monthexamin when ''A'' then 1 else 0 end)
,totalB=sum(case monthexamin when ''B'' then 1 else 0 end)
set @sql=@sql+' from 表名 group by id'
exec(@sql)
max(case month when 200401 then monthexamin else 0 end) as '200401',
max(case month when 200402 then monthexamin else 0 end) as '200402',
max(case month when 200403 then monthexamin else 0 end) as '200403',
max(case month when 200404 then monthexamin else 0 end) as '200404',
totalA=sum(case monthexamin when ''A'' then 1 else 0 end) ,
totalB=sum(case monthexamin when ''B'' then 1 else 0 end)
from t group by id
select @sql= 'select employeeid'
select @sql= @sql+',max(case yearmonth when '''+yearmonth+''' then examineid end) ['+yearmonth+']'
from (select distinct yearmonth from bonusrec ) a
set @sql=@sql+',totalA=sum(case examineid when examineid=''0002'' then 1 else 0 end),
totalB=sum(case examineid when examineid=''0003'' then 1 else 0 end),
totalC=sum(case examineid when examineid=''0004'' then 1 else 0 end),
totalD=sum(case examineid when examineid=''0005'' then 1 else 0 end)'
set @sql=@sql+ 'from bonusrec group by employeeid'
exec(@sql)我这样写了 但是还是有问题啊: 第 1 行: '=' 附近有语法错误。
怎么弄啊
set @sql='select id'
select @sql=@sql+',max(case month when '''+month+''' then monthexamin end)['+month+']'
from (select distinct month from aa) ttset @sql=@sql+',totalA=sum(case monthexamin when ''A'' then 1 else 0 end)
,totalB=sum(case monthexamin when ''B'' then 1 else 0 end)'set @sql=@sql+' from aa group by id'print @sqlexec(@sql)
set @sql='select id'
select @sql=@sql+',max(case month when '''+month+''' then monthexamin end)['+month+']'
from (select distinct month from 表名)a
set @sql=@sql+',totalA=sum(case monthexamin when ''A'' then 1 else 0 end)
,totalB=sum(case monthexamin when ''B'' then 1 else 0 end)
set @sql=@sql+' from 表名 group by id'
exec(@sql)
select id,
max(case month when 200401 then monthexamin else null end) as '200401',
max(case month when 200402 then monthexamin else null end) as '200402',
max(case month when 200403 then monthexamin else null end) as '200403',
max(case month when 200404 then monthexamin else null end) as '200404',
totalA=sum(case monthexamin when 'A' then 1 else 0 end) ,
totalB=sum(case monthexamin when 'B' then 1 else 0 end)
from t group by id
declare @sql varchar (1024),@month varchar(8)
set @sql = 'select id,'
set @month = ''
declare mycur cursor for select distinct month from test open mycur
fetch next from mycur into @month
while @@FETCH_STATUS = 0
begin
set @sql = @sql + 'max(case month when ''' + @month + ''' then monthexamin else ''0'' end) as '''+ @month + ''','
fetch next from mycur into @month
end
set @sql = @sql + 'sum(case monthexamin when ''A'' then 1 else 0 end) totalA,'
+ 'sum(case monthexamin when ''B'' then 1 else 0 end) totalB'
+ ' from test group by id'
exec(@sql)close mycur
deallocate mycur
试试这个啊