First, create a table and add data.
Expected results
-- Method 1 select distinct C. [user_name] As 'name', (select score from test_tb_grade where [user_name] = C. [user_name] and course = '') as '', (select score from test_tb_grade where [user_name] = C. [user_name] and course = 'mate') as 'mate', (select score from test_tb_grade where [user_name] = C. [user_name] and course = 'English ') as 'English' from DBO. test_tb_grade c -- method 2 select [user_name] As 'name', sum (case when course = 'China' then score end) as 'China ', sum (case when course = 'mate' then score end) as 'mate', sum (case when course = 'English 'Then score end) as 'English' from DBO. test_tb_gradegroup by [user_name] -- Method 3 declare @ s nvarchar (1000) set @ s = 'select [user_name] As ''' + 'name' + ''' select @ s = @ s + ', sum (case when course = ''' + Cast (course as varchar) + ''' then score end) as ''' + course + ''' from test_tb_grade group by course
Set @ s = @ s + 'from test_tb_grade group by [user_name] 'exec (@ s)
Recommended method 3