Shulou(Shulou.com)06/01 Report--

Preparatory work

Create a tabl

Use [test1] gocreate table [dbo]. [student] ([id] [int] identity (1p1) not null, [name] [nvarchar] (50) null, [project] [nvarchar] (50) null, [score] [int] null, constraint [pk_student] primary key clustered ([id] asc) with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]) primary [primary]

Insert data

Insert into test1.dbo.student (name,project,score) values ('Zhang San', 'android','60'), (' Zhang San', 'ios','70'), (' Zhang San', 'html5','55'), (' Zhang San', '.net', '100'), (' Li Si', 'android','60'), (' Li Si', 'ios','75'), (' Li Si', 'html5'') '90'), (' Li Si', '.net', '100')

Use Case When and aggregate functions for row-specific columns

Grammar

Select column_name, () from database.schema.tablegroup by column_name

Grammatical analysis

Column_name

Data column name

Aggregation function

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

Case when expression

Case when expression

Example

Select name,max (case project when 'android' then score end) as' Android', max (case project when 'ios' then score end) as' Apple', max (case project when 'html5' then score end) as' html5',max (case project when '.net' then score end) as'. Net'from [test1]. [dbo]. [student] group by name

Sample result

Before conversion

After conversion

Use PIVOT for row-specific columns

PIVOT rotates the table-valued expression by converting a unique value in one column of the expression to multiple columns in the output. And PIVOT runs aggregations on any remaining column values needed in the final output, PIVOT provides simpler and readable syntax than the syntax specified by a series of complex SELECT...CASE statements, and PIVOT performs aggregations and merges possible multiple lines into a single line in the output.

Grammar

Select, [first pivoted column] as, [second pivoted column] as,... [last pivoted column] as from () as pivot () for [] in ([first pivoted column], [second pivoted column],... [last pivoted column]) as

Grammatical analysis

Non-aggregate column.

[first pivoted column]

The first column name.

[second pivoted column]

The second column name.

[last pivoted column]

The last column name.

Data subtable.

Table alias.

Aggregate function.

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

[]

Transform column, the only value returned by this column will become the field in the final result set.

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

The column name to be converted for each row in the data row.

Sort rules.

Example

Select b.namemore b. [android], b. [ios], b. [html5], b. [.net] from (select Name,Project,Score from [test1]. [dbo]. [student]) as apivot (max (Score) for Project in ([android], [ios], [html5], [.net])) as border by b.name desc

Sample result

Before conversion

After conversion

Matters needing attention

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

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

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.