Mssql displays a table structure in another way (meaning unchanged)

I. Requirements

Because of poor expression ability, direct. In the mssql environment, you need to convert the table in figure 1 to the table in Figure 2:

Figure 1 Figure 2

Ii. Analysis

　　

Figure 3

2.1 create a table
Copy codeThe Code is as follows:
Create table # tb
(ID int primary key,
Class nvarchar (max ),
[Subject] nvarchar (max ),
Score int)
Insert into # tb values (1, 'one (1) class', 'China', '92 ')
Insert into # tb values (2, 'one (1) class', 'mat', '91 ')
Insert into # tb values (3, 'one (1) class', 'English ', '93 ')
Insert into # tb values (4, 'one (2) class', 'mat', '94 ')
Insert into # tb values (5, 'one (2) class', 'China', '95 ')
Insert into # tb values (6, 'one (2) class', 'English ', '96 ')
Insert into # tb values (7, 'one (3) class', 'mat', '94 ')
Insert into # tb values (8, 'one (3) class', 'China', '96 ')
Insert into # tb values (9, 'one (3) class', 'English ', '97 ')
Select * from # tb

2.2 convert the first table in Figure 3 to the second table
Copy codeThe Code is as follows:
Select [Subject], [Class 1 (1)] = [Score]
, [One (2) Class] = [Score]
, [Class 1 (Class 3)] = [Score]
From # tb

2.3 convert the second table in Figure 3 to the third table
Copy codeThe Code is as follows:
-- Observe the second table. Obviously, many of the table's scores are incorrect, so we need to eliminate the errors.

-- How to eliminate the error: first set the incorrect score to 0, and then take the maximum number of each row as the score, and display it in groups according to the subject.
Select subject, [one (1) Class] = case when [Class] = 'one (1) class' then [Score] else 0 end
, [One (2) Class] = case when [Class] = 'one (2) class' then [Score] else 0 end
, [One (3) Class] = case when [Class] = 'one (3) class' then [Score] else 0 end
From # tb

2.4 convert the third table in Figure 3 to the fourth table
Copy codeThe Code is as follows:
Select subject, [one (1) Class] = max (case when [Class] = 'one (1) class' then [Score] else 0 end)
, [One (2) Class] = max (case when [Class] = 'one (2) class' then [Score] else 0 end)
, [One (3) Class] = max (case when [Class] = 'one (3) class' then [Score] else 0 end)
From # tb group by subject
 
2.5 simplify the above writing
Copy codeThe Code is as follows:
Declare @ s nvarchar (4000)
Set @ s =''
Select @ s = @ s + ',' + quotename (Class) + '= max (case when [Class] =' + quotename (Class ,'''') + 'then [Score] else 0 end )'
From # tb group by Class
-- Print @ s
Exec ('select [Subject] '+ @ s +' from # tb group by [Subject] ')

Iii. Simplified Analysis and Demand Extension

3.1 Simplified Analysis

The above simplification is still very difficult for me at the cainiao level, so I analyzed it step by step using print @ s after knowing the answer, especially in select @ s = @ s + ..... here I found a method to connect a single column of data to a string.

Figure 4

3.2 Demand Extension

Perhaps the above requirement is too simple, so we are now introducing complex requirements to dynamically obtain class scores. For example, our requirement may be to list the scores of some classes. The Code is as follows:
Copy codeThe Code is as follows:
Declare @ s nvarchar (4000)
Set @ s =''
Select @ s = @ s + ',' + quotename (Class) + '= max (case when [Class] =' + quotename (Class ,'''') + 'then [Score] else 0 end )'
From # tb
Where Class in (the Class you want)
Group by Class
-- Print @ s
Exec ('select [Subject] '+ @ s +' from # tb group by [Subject] ')

Iv. Summary

This article mainly implements another table display, which is usually used in the example of dynamically displaying data. The difficulty lies in the analysis process and some basic SQL syntax.