Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to convert row data into column data in SQL Server, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

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

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.

After reading the above, do you have any further understanding of how to convert row data into column data in SQL Server? If you want to know more knowledge or related content, please follow the industry information channel, 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.