insertintotestselect'2006级期末考','20080101','张三','语文','101'insertintotestselect'2006级期末考','20080202','李四','语文','109'insertintotestselect'2006级期末考','20080303','王五','语文','100'insertintotestselect'2006级期末考','20080101','张三','数学','101'insertintotestselect'2006级期末考','20080202','李四','数学','85'insertintotestselect'2006级期末考','20080303','王五','数学','58'insertintotestselect'2006级期末考','20080101','张三','政治','56'insertintotestselect'2006级期末考','20080202','李四','政治','56'insertintotestselect'2006级期末考','20080303','王五','政治','50'insertintotestselect'2006级期末考','20080101','张三','英语','102'insertintotestselect'2006级期末考','20080202','李四','英语','110'insertintotestselect'2006级期末考','20080303','王五','英语','93'go--测试declare@sqlvarchar(8000)set@sql='select A1,A2,A3'select@sql=@sql+',sum(case when A4='''+A4+'''then A5 else 0 end)['+A4+']'from(selectdistinctA4fromtest)aset@sql=@sql+'from test group by A1,A2,A3 order by A1,A2,A3'exec(@sql)--删除测试环境droptabletestsetnocountoff/*--
A1 A2 A3 数学 英语 语文 政治
-------------------- -------------------- -------------------- ---------------------- ---------------------- ---------------------- ----------------------
2006级期末考 20080101 张三 101 102 101 56
2006级期末考 20080202 李四 85 110 109 56
2006级期末考 20080303 王五 58 93 100 50*/