create table A(ID int,username nvarchar(10))
insert A select 1, '张军'
union all select 2, '李南'create table B(ID int,course nvarchar(10))
insert B select 1, '语文'
union all select 2, '数学'
create table C(ID int,userID int,courseID int,score int)
insert C select 1, 1, 1, 60
union all select 2, 1, 2, 70
union all select 3, 2, 1, 65
union all select 4, 2, 2, 73 declare @sql nvarchar(4000)
set @sql='select userID,'
select @sql=@sql+quotename(course,']')+'=sum(case when courseID='+rtrim(ID)+' then score end),'
from B
select @sql=left(@sql,len(@sql)-1),@sql=@sql+' from C group by userID 'exec(@sql)
insert A select 1, '张军'
union all select 2, '李南'create table B(ID int,course nvarchar(10))
insert B select 1, '语文'
union all select 2, '数学'
create table C(ID int,userID int,courseID int,score int)
insert C select 1, 1, 1, 60
union all select 2, 1, 2, 70
union all select 3, 2, 1, 65
union all select 4, 2, 2, 73 declare @sql nvarchar(4000)
set @sql='select userID,'
select @sql=@sql+quotename(course,']')+'=sum(case when courseID='+rtrim(ID)+' then score end),'
from B
select @sql=left(@sql,len(@sql)-1),@sql=@sql+' from C group by userID 'exec(@sql)
http://community.csdn.net/Expert/topic/5561/5561486.xml?temp=.5522882