Pivot and UNPIVOT functions are the new 2 functions provided by SQL05

Pivot and UNPIVOT functions are the new 2 functions provided by SQL05

------------------------------------------------------------------------------

Pivot function:

CREATE TABLE Test (ID int,name varchar (), Quarter int,profile Int)
INSERT into test values (1, ' a ', 1,1000)
INSERT into test values (1, ' a ', 2,2000)
INSERT into test values (1, ' a ', 3,4000)
INSERT into test values (1, ' a ', 4,5000)
INSERT into test values (2, ' B ', 1,3000)
INSERT into test values (2, ' B ', 2,3500)
INSERT into test values (2, ' B ', 3,4200)
INSERT into test values (2, ' B ', 4,5500)

SELECT * FROM test--create TABLE test

Now you need to turn quarter from 1 columns of data into 4 column data effects such as:

Splitting a column into columns can be accomplished using the pivot function very simply.

SELECT * FROM Test
Pivot
(
SUM ([profile]) for [quarter]
Inch
([1],[2],[3],[4])
)
As
S

Note: When using pivot to split a column into columns, the following as takes the individual name, which is a fixed format and is required to use an aggregate function.

Of course, you can get the same effect without using the pivot function just because the code is long-cut inefficient but easy to understand

Select Id,[name],
' 1 ' = (select sum ([profile]) from test where id=a.id and Quarter=1),
' 2 ' = (select sum ([profile]) from test where id=a.id and quarter=2),
' 3 ' = (select sum ([profile]) from test where id=a.id and quarter=3),
' 4 ' = (select sum ([profile]) from test where id=a.id and quarter=4)
From Test as a
GROUP BY Id,name

-----------------------------------------------------------------------------------------

Unpivot function as the name implies, he's just merging a few columns into 1 columns.

CREATE TABLE test1 (ID int,name varchar (), Q1 int, Q2 int, Q3 int, Q4 int)

INSERT into test1 values (1, ' a ', 1000,2000,4000,5000)
INSERT into test1 values (2, ' B ', 3000,3500,4200,5500)

SELECT * FROM Test1--Create test1 table

We're going to put Q1 Q2 Q3 Q4 into the 1-column quarterly column as the effect:

The use of Unpivot can be very simple to implement

Select ID, [Name],[jidu],[xiaoshou] from Test1
Unpivot
(
Xiaoshou for Jidu in
([Q1],[q2],[q3],[q4])
)
As F

Note: Also need to use as Alias is also fixed format Unpivot function does not have an aggregate function Xiaoshou and Jidu columns are not the original Jidu table is composed of the original Q1 Q2 Q3 Q4

The same does not use UNPIVOT can also achieve the above functions

Select Id,[name],
Jidu= ' Q1 ',
xiaoshou= (select Q1 from test1 where id=a.id)
From Test1 as a
Union
Select Id,[name],
Jidu= ' Q2 ',
xiaoshou= (select Q2 from test1 where id=a.id)
From Test1 as a
Union
Select Id,[name],
Jidu= ' Q3 ',
xiaoshou= (select Q3 from test1 where id=a.id)
From Test1 as a
Union
Select Id,[name],
Jidu= ' Q4 ',
xiaoshou= (select Q4 from test1 where id=a.id)
From Test1 as a

Pivot and UNPIVOT functions are the new 2 functions provided by SQL05