SQL built-in function pivot powerful row to column function

Original: SQL built-in function pivot powerful row-to-column function

Grammar:

PIVOT used to rotate column values to column names (row to column) in SQL Server - you can use aggregate functions to mate Case Statement Implementation

PIVOT The general syntax is: PIVOT ( aggregate function (column) for column in (...)) As P

Full syntax:

Table_source

PIVOT (

Aggregation Functions ( Value_column )

For Pivot_column

In (<column_list>)

)

UNPIVOT used to convert a column to a column value (that is, a column change), SQL Server can be used UNION to achieve

Full syntax:

Table_source

UNPIVOT (

Value_column

For Pivot_column

In (<column_list>)

)

Note: PIVOT, Unpivot is SQL Server 2005 syntax to use to modify the database compatibility level
on the database properties, options, compatibility level, change to

-----------------------------------------------------------------------------------------a gorgeous delimiter-------------------------- -----------------------------------------------------------------------

The simplest case: a word conversion

Build table:

If object_id (' TB ') is not null drop table TB
Go
CREATE table TB (name varchar (10), course varchar (10), fractional int)
INSERT into TB values (' Zhang San ', ' language ', 74)
INSERT into TB values (' Zhang San ', ' math ', 83)
INSERT into TB values (' Zhang San ', ' physical ', 93)
INSERT into TB values (' John Doe ', ' language ', 74)
INSERT into TB values (' John Doe ', ' math ', 84)
INSERT into TB values (' John Doe ', ' physical ', 94)
Go

SELECT * FROM TB

-------------------------------- Results -------------------------------------------------------------------- ----------------

Name Course Score
---------- ---------- -----------
Zhang San language 74
Zhang San Mathematics 83
Zhang San Physics 93
John Doe Language 74
John Doe Mathematics 84
John Doe Physics 94

(6 rows affected)

Now the question is: I would like to count the person's achievements by name, namely: The Name Chinese mathematics physics

First look at the time when using the case and end structure:

Select Name,
Max (case course when ' language ' then score else 0 end) language,
Max (case course when ' math ' then fraction else 0 end) Math,
Max (case course when ' physical ' then fraction else 0 end) physical
From TB
Group BY name

-------------------------------- Results -------------------------------------------------------------------- ----------------

Name Chinese mathematics Physics
---------- ----------- ----------- -----------
Lee 474 84 94
Sheet 374 83 93

(2 rows affected)

The result is what we want, and then we'll look at using pivot:

SELECT * FROM TB pivot (MAX (score) for course in (language, mathematics, physics)) a

--------------------------------Results------------------------------------------------------------------------------------

Name Chinese mathematics Physics
---------- ----------- ----------- -----------
Lee 474 84 94
Sheet 374 83 93

(2 rows affected)

Wow, the result is exactly the same! This is the result I want to use.

SQL built-in function pivot powerful row to column function