SQL Server row-to-column processing example

It mainly uses case statements to solve the problem of Row-to-column conversion.

The row-to-column conversion problem is mainly divided into two types:

1) Simple row-to-column conversion:

Example Table:

Id Sid Course Result

1 2005001 Chinese 80.0
2 2005001 mathematics 90.0
3 2005001 English 80.0
4 2005002 Chinese 56.0
2005002 mathematics 69.0
6 2005002 English 89.0

Run

Select Sid, language = isnull (sum (Case course when 'China' then result end), 0 ),
Mathematics = isnull (sum (Case course when 'mate' then result end), 0 ),
English = isnull (sum (Case course when 'English 'Then result end), 0)
From result
Group by Sid
Order by Sid

Result

Sid Chinese maths English

2005001 80.0 90.0 80.0
2005002 56.0 69.0 89.0

2) Complicated row-to-column Conversion

Table 1: Course

ID name

1 Language
2. Mathematics
3 English

Table 2: Result

Id Sid Course Result

1 2005001 Chinese 80.0
2 2005001 mathematics 90.0
3 2005001 English 80.0
4 2005002 Chinese 56.0
2005002 mathematics 69.0
6 2005002 English 89.0

Declare @ SQL varchar (8000)
Set @ SQL = 'select Sid'
Select @ SQL = @ SQL + ',' + course. name + '= isnull (sum (Case course when ''' + course. name + ''' then result end), 0 )'
From course order by ID
Set @ SQL = @ SQL + 'from result group by SID order by Sid'
Print @ SQL
Exec (@ SQL)

Result

Sid Chinese maths English

2005001 80.0 90.0 80.0
2005002 56.0 69.0 89.0