Suppose there is a student score table (TB) as follows:
Name Subject Result
John Language 74
John Math 83
John Physics 93
Dick Language 74
Dick Math 84
Dick Physics 94
Want to become
Name Chinese mathematics Physics
---------- ----------- ----------- -----------
Lee 474 84 94
Zhang 374 83 93
The SQL statement is as follows:
Copy Code code as follows:
CREATE TABLE TB
(
Name varchar (10),
Subject varchar (10),
result int
)
INSERT into TB (Name, Subject, result) VALUES (' John ', ' language ', 74)
INSERT into TB (Name, Subject, result) VALUES (' John ', ' math ', 83)
INSERT into TB (Name, Subject, result) VALUES (' John ', ' Physics ', 93)
INSERT into TB (Name, Subject, result) values (' Dick ', ' language ', 74)
INSERT into TB (Name, Subject, result) values (' Dick ', ' math ', 84)
INSERT into TB (Name, Subject, result) values (' Dick ', ' physics ', 94)
Go
--static SQL, refers to subject only language, mathematics, physics, the three courses.
Select name Name,
Max (case Subject when ' language ' then result else 0) language,
Max (case Subject when ' math ' then result else 0 ") Mathematics,
Max (case Subject when ' physical ' then result else 0 end) Physics
From TB
Group BY name
--Dynamic SQL, refers to subject more than the language, mathematics, physics, the three courses.
DECLARE @sql varchar (8000)
Set @sql = ' Select name as ' + ' name '
Select @sql = @sql + ', max (case Subject when ' + Subject + ' "then result else 0 end) [' + Subject + '] '
From (select distinct Subject from TB) as a
Set @sql = @sql + ' from TB GROUP by name '
EXEC (@sql)
