declare @sql varchar(8000)
set @sql = 'select code'
select @sql = @sql + ',sum(case xk when '''+GZLB+''' then cj end) ['+xk+']'
from (select distinct xk from #test) as aselect @sql = @sql+' from #test group by code'
select @sql
exec(@sql)
set @sql = 'select code'
select @sql = @sql + ',sum(case xk when '''+GZLB+''' then cj end) ['+xk+']'
from (select distinct xk from #test) as aselect @sql = @sql+' from #test group by code'
select @sql
exec(@sql)
inner join (select code,cj from tb where xk='語文')b on a.code=b.code
inner join (select code,cj from tb where xk='數學')c on a.code=c.code
inner join (select code,cj from tb where xk='外語')d on a.code=d.code
,sx=(select cj from 学生表 where xk='数学' and code=b.code)
,sx=(select cj from 学生表 where xk='外语' and code=b.code)
from 学生表 b
group by code
set @sql = 'select code'
select @sql = @sql + ',sum(case xk when '''+rtrim(xk)+''' then cj else 0 end) ['+rtrim(xk)+']'
from tablename
group by xk
select @sql = @sql+' from tablename group by code'
exec(@sql)
select @sql = @sql + ',sum(case xk when '''+GZLB+''' then cj end) '+xk+''
from (select distinct xk from 表) as a
select @sql ='select code '+ @sql+' from 表 group by code'
exec(@sql)
set @sql = 'select code'
select @sql = @sql + ',sum(case xk when '''+GZLB+''' then cj end) ['+xk+']'
from (select distinct xk from #test) as aselect @sql = @sql+' from #test group by code'
select @sql
exec(@sql)
create table aa(code varchar(10), cj int , xk varchar(10))insert aa select '101' , 70 , '语文'
insert aa select '101' , 80 , '数学'
insert aa select '101' , 75 , '外语'
insert aa select '102' , 70 , '语文'
insert aa select '102' , 90 , '数学'
select code ,
语文=max(case xk when '语文' then cj end),
数学=max(case xk when '数学' then cj end),
外语=max(case xk when '外语' then cj end)
from aa
group by code
@str = @str + 'case xk when '"+ xk +"' then cj else 0 end '
@str = @str + ' from (select distinct * from tab_name) as aa '
select @str
exec @str
insert into @tb1
select '101',70,'语文' union all
select '101',80,'数学' union all
select '101',75,'外语' union all
select '102',70,'语文' union all
select '102',90,'数学'
select a.code as 学号,b.cj as 语文,c.cj as 数学,d.cj as 外语 from (select distinct code from @tb1)a
left outer join (select code,cj from @tb1 where xk='语文')b on a.code=b.code
left outer join (select code,cj from @tb1 where xk='数学')c on a.code=c.code
left outer join (select code,cj from @tb1 where xk='外语')d on a.code=d.code
set @sql = 'select code'
select @sql = @sql + ',sum(case xk when '''+GZLB+''' then cj end) ['+xk+']'
from (select distinct xk from #test) as aselect @sql = @sql+' from #test group by code'
select @sql
exec(@sql应该是.
如果确实不知道原理,你把exec(@SQL)改为print (@SQL) 看看.就知道了
sum(case xk when 语文 then cj else 0 end) as 'yw(语文)',
sum(case xk when 数学 then cj else 0 end) as 'sx(数学)',
sum(case xk when 数学 then cj else 0 end) as 'wy(外语)'
from table_name
group by code
order by code