Dead work

Create tables

 1 use [test1]
 2 go
 3 
 4 create table [dbo].[student](
 5     [id] [int] identity(1,1) not null,
 6     [name] [nvarchar](50) null,
 7     [project] [nvarchar](50) null,
 8     [score] [int] null,
 9  constraint [pk_student] primary key clustered 
10 (
11     [id] asc
12 )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
13 ) on [primary]
14 go

insert data

1 insert into test1.dbo.student(name,project,score)
2 values('Zhang San','android','60'),
3       ('Zhang San','ios','70'),
4       ('Zhang San','html5','55'),
5       ('Zhang San','.net','100'),
6       ('Li Si','android','60'),
7       ('Li Si','ios','75'),
8       ('Li Si','html5','90'),
9       ('Li Si','.net','100');

Use Case When and Aggregation Functions for row-specific columns

grammar

1 select column_name,
2 <aggregation function>(<case when expression>)  
3 from database.schema.table
4 group by column_name

Grammatical analysis

column_name

Data Column Name

aggregation function

Aggregation functions, common are: sum,max,min,avg,count and so on.

case when expression

case when expression

Example

1 select name,
2 max(case project when 'android' then score end) as 'Android',
3 max(case project when 'ios' then score end) as 'Apple',
4 max(case project when 'html5' then score end) as 'html5',
5 max(case project when '.net' then score end) as '.net'
6 from [test1].[dbo].[student]
7 group by name

Example results

Before conversion

After conversion

Use PIVOT for row-specific columns

PIVOT rotates table-valued expressions by converting unique values in a column of an expression into multiple columns in the output. PIVOT runs aggregation on any remaining column values required in the final output. PIVOT provides a simpler and more readable syntax than the syntax specified by a series of complex SELECT...CASE statements. PIVOT performs aggregation and merges possible multiple rows into a single row in the output.

Grammar

 1 select <non-pivoted column>,  
 2     [first pivoted column] as <column name>,  
 3     [second pivoted column] as <column name>,  
 4     ...  
 5     [last pivoted column] as <column name>  
 6 from  
 7     (<select query that produces the data>)   
 8     as <alias for the source query>  
 9 pivot  
10 (  
11     <aggregation function>(<column being aggregated>)  
12 for   
13 [<column that contains the values that will become column headers>]   
14     in ( [first pivoted column], [second pivoted column],  
15     ... [last pivoted column])  
16 ) as <alias for the pivot table>  
17 <optional order by clause>;  

Grammatical analysis

<non-pivoted column>

Non-aggregated columns.

[first pivoted column]

First column.

[second pivoted column]

Column 2.

[last pivoted column]

The last column.

<select query that produces the data>

Data subtable.

<alias for the source query>  

Table alias.

<aggregation function>

Aggregation function.

<column being aggregated>

Aggregate function columns are used to output value columns, which are grouped by the columns returned in the final output (called grouping columns).

[<column that contains the values that will become column headers>] 

Convert a column that returns a unique value that will become a field in the final result set.

[first pivoted column], [second pivoted column],  ... [last pivoted column]

Column names to be converted for each row in a data row.

<optional order by clause>

Sorting rules.

Example

 1 select b.Name,b.[android],b.[ios],b.[html5],b.[.net] 
 2 from  
 3 (select Name,Project,Score from [test1].[dbo].[student])
 4 as a
 5 pivot
 6 (
 7     max(Score)
 8     for Project in ([android],[ios],[html5],[.net])
 9 ) 
10 as b
11 order by b.name desc

Example results

Before conversion

After conversion

Matters needing attention

1. If the output column name cannot be in the table conversion column, no calculation will be performed.

2. The data types of column names of all columns output must be consistent.