SQL Server Table cross display and implementation method

Suppose there is a student score table (T) as follows:

Name Subject Result

John Language 73

John Math 83

John Physics 93

Dick Language 74

Dick Math 84

Dick Physics 94

Want to become

Name Chinese mathematics Physics

Zhang 373 83 93

Lee 474 84 94

Code

CREATE TABLE #t

(

Name varchar (10),

Subject varchar (10),

result int

)

Insert into #t (Name, Subject, result) VALUES (' John ', ' language ', ' 73 ')

Insert into #t (Name, Subject, result) VALUES (' John ', ' Math ', ' 83 ')

Insert into #t (Name, Subject, result) VALUES (' John ', ' Physics ', ' 93 ')

Insert into #t (Name, Subject, result) values (' Dick ', ' language ', ' 74 ')

Insert into #t (Name, Subject, result) values (' Dick ', ' math ', ' 83 ')

Insert into #t (Name, Subject, result) values (' Dick ', ' physics ', ' 93 ')

DECLARE @sql varchar (8000)

Set @sql = ' Select name as name '

Select @sql = @sql + ', sum (case Subject when ' + Subject + ' "then result end) [' + Subject + '] '

From (select distinct Subject from #t) as a

Set @sql = @sql + ' from #t GROUP by name '

EXEC (@sql)

drop table #t

--The result

Name Mathematics Physics Language

---------- ----------- ----------- -----------

Lee 483 93 74

Zhang 383 93 73

If the above two tables change each other: namely

Name Chinese mathematics Physics

Zhang 373 83 93

Lee 474 84 94

Want to become

Name Subject Result

John Language 73

John Math 83

John Physics 93

Dick Language 74

Dick Math 84

Dick Physics 94

Code

CREATE TABLE #t

(

Name varchar (10),

language int,

math int,

Physical int

)

Insert into #t (name, language, maths, physics) VALUES (' John ', 73,83,93)

Insert into #t (name, language, maths, physics) VALUES (' Dick ', 74,84,94)

Select name as name, ' language ' as Subject, language as result from #t Union

Select name as name, ' math ' as Subject, math as result from #t Union

Select name as name, ' physical ' as Subject, physical as result from #t

ORDER BY name Desc

drop table #t

Name Subject Result

---------- ------- -----------

John Math 83

John Physics 93

John Language 73

Dick Math 84

Dick Physics 94

Dick Language 74

(The number of rows affected is 6 rows)