I. Demand
Because the ability to express is not good, directly above the picture. In the MSSQL environment, you need to convert the table in Figure 1 to the table in Figure 2:
Figure 1 Figure 2
Second, analysis
Figure 3
2.1 Creating a table
Copy Code code 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 ', ' language ', ' 92 ')
INSERT into #tb values (2, ' One (1) class ', ' math ', ' 91 ')
INSERT into #tb values (3, ' One (1) class ', ' English ', ' 93 ')
INSERT into #tb values (4, ' One (2) class ', ' math ', ' 94 ')
INSERT into #tb values (5, ' One (2) class ', ' language ', ' 95 ')
INSERT into #tb values (6, ' One (2) class ', ' English ', ' 96 ')
INSERT into #tb values (7, ' One (3) class ', ' math ', ' 94 ')
INSERT into #tb values (8, ' One (3) class ', ' language ', ' 96 ')
INSERT into #tb values (9, ' One (3) class ', ' English ', ' 97 ')
SELECT * FROM #tb
2.2 Convert the first table in Figure 3 into the second table
Copy Code code as follows:
Select [Subject], [One (1) class]=[score]
, [One (2) class]=[score]
, [One (3) class]=[score]
From #tb
2.3 Convert the second table in Figure 3 into the third table
Copy Code code as follows:
--Watch the second table, the table clearly many of the results are wrong, so to eliminate the error
--Eliminate the wrong way: first set the wrong score to 0, then each row to take the largest number as a result, and grouped by the subject to display
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 table fourth
Copy Code code 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 simplifying the above wording
Copy Code code 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. simplification of analysis and extension of requirements
3.1 Simplified Analysis
The simplification above, for the rookie level, I understand that is still very difficult, so I know the answer, with the print @s step-by-step analysis, especially in select @s=@s+ ... Time, where I found a way to concatenate a single column of data into a string.
Figure 4
3.2 Demand Extension
Perhaps the above demand is too simple, then we now draw a complex point of demand, dynamic acquisition of class results, such as our needs may be listed some of the class results, the code is as follows:
Copy Code code 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 display of the table, usually used for dynamic display of data examples. The difficulty is in the analysis process and some basic SQL syntax.
