SQL statement for table row-to-column Conversion

SQL statement for table row-to-column Conversion
Tabele has a lot of information to be searched online. You can use online copy to implement the functions you want. But after implementing the function, have you ever wondered why the row-to-column conversion function should be written like this? The following is an example for analysis, hoping to help beginners.

I. Requirements
1. Create a data table
Create Table [DBO]. [stuscore] (
[Stuid] [int] not null,
[Subject] [nvarchar] (30) null,
[Score] [decimal] (5, 1) null
)

2. Insert Test Data
Stuid subject score
3 Chinese 76.0
3 math 73.0
4 Chinese 82.0
5 Chinese 66.0
5 math 93.0
6 Chinese 67.0
7 math 83.0
8 Chinese 77.0
8 math 84.0

Result After three rows are converted into columns
Stuid Chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0

8 77.0 84.0

 

Ii. Analysis
One line to column, one key is how to know how many columns, how to create these columns? We can put this problem on hold first, and assume these columns are known. For example, in the sample data, we can first assume that the subject data [Chinese, math] is known, which simplifies many

2 after learning about Chinese and math, we must at least obtain the converted tabel structure
as follows;
select stuid, 0 as Chinese, 0 as math from DBO. stuscore
result
stuid Chinese math
3 0 0
3 0 0
4 0 0 0
5 0 0
5 0 0
6 0 0
7 0 0
8 0 0
8 0 0

3 then we need to fill in the data set of Chinese and math.
select stuid,
case subject when 'chine' then score else 0 end as Chinese,
case subject when 'Math' then score else 0 end as math
from DBO. stuscore
The result is as follows:
stuid Chinese math
3 76.0 0.0
3 0.0 73.0
4 82.0 0.0
5 66.0 0.0
5 0.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 0.0
8 0.0 84.0

4. careful readers will find that the result in step 3 is very close to what we want. Just perform a sum () processing and it will be OK.
Select stuid,
Sum (case subject when 'China' then score else 0 end) as Chinese,
Sum (case subject when 'Math' then score else 0 end) as math
From DBO. stuscore group by stuid
The result is exactly what we want.
Stuid Chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0

Is it done now? The answer is no. As we have already said before, it is to simplify the problem. If we already know the subject data, the data processed in this way is actually changeable and unknown, the next step is to solve this problem.

5. It is actually very easy to get the data of subject.
Select distinct subject from DBO. stuscore
How can I obtain the case subject when 'China' then score else 0 end statement after obtaining it?
You can dynamically group SQL statements based on the value of subject.
See the following section.Code

Declare @ SQL varchar (2000)
Set @ SQL =''
Select @ SQL = @ SQL + ', case subject when ''' + Subject + ''then 1 else 0 end as' + Subject
From (select distinct subject from DBO. stuscore) as sub
Print @ SQL

Message:
, Case subject when 'China' then 1 else 0 end as Chinese, case subject when 'Math' then 1 else 0 end as math

6. Finally, we need to combine the previous steps to obtain the final SQL

Declare @ SQL varchar (2000)
Set @ SQL = 'select stuid'
Select @ SQL = @ SQL + ', sum (case subject when ''' + Subject + ''' then score else 0 end) as' + Subject
From (select distinct subject from DBO. stuscore) as sub
Set @ SQL = @ SQL + 'from DBO. stuscore group by stuid'
Exec (@ SQL)

stuid Chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0