Create Table Allscore (ID Int Identity ( 1 , 1 ) Primary Key , -- Primary Key Stu_name Varchar ( 20 ), -- Student name Category Varchar ( 20 ), -- Subject Record Float , -- Score ) /* Insert data */ Insert Into Allscore (stu_name, category, record) Values (' Andy Lau ' , ' Chinese ' , 100 ) Insert Into Allscore (stu_name, category, record) Values ( ' Andy Lau ' , ' English ' , 95 ) Insert Into Allscore (stu_name, category, record) Values ( ' Andy Lau ' , ' Mathmatics ' , 98 ) Insert Into Allscore (stu_name, category, record) Values ( ' Arnold ' , ' Chinese ' , 90 ) Insert Into Allscore (stu_name, category, record) Values ( ' Arnold ' , ' English ' , 95 ) Insert Into Allscore (stu_name, category, record) Values ( ' Arnold ' , ' Mathmatics ' ,14 ) Insert Into Allscore (stu_name, category, record) Values ( ' Taishang laojun ' , ' Chinese ' , 70 ) Insert Into Allscore (stu_name, category, record)Values ( ' Taishang laojun ' , ' English ' , 95 ) Insert Into Allscore (stu_name, category, record) Values ( ' Taishang laojun ' ,' Mathmatics ' , 57 ) Insert Into Allscore (stu_name, category, record) Values ( ' Bidagolas ' , ' Chinese ' , 60 ) Insert Into Allscore (stu_name, category, record) Values ( ' Bidagolas ' , ' English ' , 95 ) Insert Into Allscore (stu_name, category, record) Values (' Bidagolas ' , ' Mathmatics ' , 68 ) Insert Into Allscore (stu_name, category, record) Values ( ' Plato ' , ' Chinese ' , 60 ) Insert Into Allscore (stu_name, category, record) Values ( ' Plato ' , ' English ' , 95 ) Insert Into Allscore (stu_name, category, record) Values ( ' Plato ' , ' Mathmatics ' , 78 ) Insert Into Allscore (stu_name, category, record) Values (' Aristotle ' , ' Chinese ' , 40 ) Insert Into Allscore (stu_name, category, record) Values ( ' Aristotle ' , ' English ' , 22 ) Insert Into Allscore (stu_name, category, record) Values ( ' Aristotle ' , ' Mathmatics ' , 25 ) Insert Into Allscore (stu_name, category, record) Values ( ' Rousseau ' , ' Chinese ' , 40 ) Insert Into Allscore (stu_name, category, record) Values ( ' Rousseau ' , ' English ' , 50 ) Insert Into Allscore (stu_name, category, record) Values ( ' Rousseau ' , ' Mathmatics ' ,78 ) Insert Into Allscore (stu_name, category, record) Values ( ' Laozhuang ' , ' Chinese ' , 100 ) Insert Into Allscore (stu_name, category, record)Values ( ' Laozhuang ' , ' English ' , 20 ) Insert Into Allscore (stu_name, category, record) Values ( ' Laozhuang ' ,' Mathmatics ' , 98 ) -- --- Start row-to-column conversion ------- Declare @ SQL Nvarchar ( 4000 ) Set @ SQL = ' Select stu_name ' Select @ SQL = @ SQL + ' , Sum (case when Category = ''' + Category + ''' Then record else 0 end) ''' + Category+ '''' From Allscore Group By Category Set @ SQL = @ SQL + ' From allscore group by stu_name ' Print @ SQL Execute Sp_executesql @ SQL