SQL Server row to column

Grammar:

Pivot is used to rotate column values to column names (row to column), which can be implemented in SQL Server in conjunction with a case statement with an aggregate function

the general syntax for pivot is:pivot( aggregate function (column) for column in (...)) As P

Full syntax:

Table_source

PIVOT (

Aggregation function (value_column)

For Pivot_column

In (<column_list>)

)

The UNPIVOT is used to set the column value to a column (that is, a column change), in SQL Server a UNION can be used to implement

Full syntax:

Table_source

UNPIVOT (

Value_column

For Pivot_column

In (<column_list>)

)

Note: PIVOT, Unpivot is the syntax for SQL Server 2005 , use the database compatibility level that you want to modify
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)

SQL Server row to column