Use COALESCE + SUM + CASE

Problem: Change the table to table 2 Table 1: stdnamestdsubjectresult Zhang San Language 80 Zhang San mathematics 90 Zhang San physics 85 Li Si language 85 Li Si mathematics 92 Li Si physics 82 Li Si chemistry 82 table 2: stdname Chinese Mathematics Physical Chemistry Li Si 85928282 Zhang San 8090850 Problem Analysis: Use grouping to solve the problem

Problem: Change the table to table 2 Table 1: stdname stdsubject result Zhang San Language 80 Zhang San mathematics 90 Zhang San physics 85 Li Si language 85 Li Si mathematics 92 Li Si physics 82 Li Si chemistry 82 table 2: stdname Chinese Mathematics Physical Chemistry Li Si 85 92 82 82 Zhang San 80 90 85 0 Problem Analysis: Use grouping to solve the problem

Problem: change a table to table 2

Table 1:

Stdname stdsubject result
Zhang San Chinese 80
Zhang San, mathematics 90
Zhang San physical 85
Li Si Chinese 85
Li Si mathematics 92
Li Si physical 82
Li Si chemistry 82

Table 2:
Stdname Chinese Mathematics Physical Chemistry
Li Si 85 92 82 82
Zhang San 80 90 85 0

Problem Analysis: If grouping is used to solve the problem, the written SQL statement is in this format:

SELECT stdname,... FROM # student group by stdname

In the middle... how to write it?

To use the record value of Table 1 as the field of table 2, you need to use the CASE statement. However, there is no chemical score in table 3,

Therefore, COALESCE is required.

SQL code:

Create table # student (stdname nvarchar (10), stdsubject nvarchar (10), result int) insert into # student VALUES ('zhang san', 'China', 80) insert into # student values ('zhang san', 'mat', 90) insert into # student VALUES ('zhang san', 'Physical ', 85) insert into # student VALUES ('Li si', 'China', 85) insert into # student values ('Li si', 'mat', 92) insert into # student VALUES ('lily', 'Physical ', 82) insert into # student VALUES ('lily', 'Chemical', 82)

-- Use COALESCE + SUM + case select stdname, COALESCE (SUM (CASE stdsubject WHEN 'Chemical' THEN Result END), 0) chemistry, COALESCE (SUM (CASE stdsubject WHEN 'mate' THEN Result END), 0) mathematics, COALESCE (SUM (CASE stdsubject WHEN 'physical 'THEN Result END), 0) physical, COALESCE (SUM (CASE stdsubject WHEN 'en 'THEN Result END), 0) language FROM # student group by stdname -- easier to use concatenated SQL -- assign values to variables using SELECT, all records are retrieved from the table repeatedly. This is the DECLARE @ SQL VARCHAR (1000) SET @ SQL = 'select stdname' SELECT @ SQL = @ SQL + 'that cannot be completed by the SET statement ', COALESCE (SUM (CASE stdsubject WHEN ''' + stdsubject + ''' THEN Result END), 0) ['+ stdsubject +'] 'FROM (select distinct stdsubject FROM # student) tempSET @ SQL = @ SQL + 'from # student group by stdname' EXEC (@ SQL)

* Tables are not designed in actual applications. Here, an execution method is provided.