SQL statement for table row-to-column Conversion

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
The result is as follows:
Stuid chinese math
3 0 0
3 0 0
4 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 the chinese and math data in this data set.
Select stuid,
Case subject when 'China' 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 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
So far, the entire analysis process and results have come out.
I want to write an article in my first attempt.