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