SQL Pivot function helps us to combine and
compare of larger amount of data and it transfer the data from rows into
columns and it will helpful in writing for cross-tabulation output.
Syntax :
SELECT
Non-PIVOT
AND PIVOT COLUMNS
FROM
(
PIVOT DATA eg. Select Query
)
AS
PIVOT
(
aggregation
function eg. SUM, Avg or Max
FOR
[]
IN ( SETS OF PIVOT COLUMNS)
)
AS
Here is an example of SQL Pivot operator how
does it convert the each employee hours as task wise report
EMP Table: It contains employee records
EMP_ID
|
EMP_NAME
|
1
|
SLOOPY
JOE
|
2
|
BILL
GATES
|
3
|
FRED
FLINTSTONE
|
TASK Table: It is lookup table for task
TASK_CD
|
TASK_DESC
|
A
|
ADMIN
|
D
|
DESIGN
|
C
|
CODING
|
F
|
FUNCTION
TEST
|
Q
|
QA
|
EMPLOYEEHOURS: This table contains daily
employee hours as task wise
DATE
|
EMP_ID
|
TASK_CD
|
HOURS
|
4/1/2013
|
1
|
D
|
10
|
4/2/2013
|
1
|
C
|
8
|
4/3/2013
|
1
|
C
|
8
|
4/4/2013
|
1
|
F
|
8
|
4/5/2013
|
1
|
C
|
8
|
4/7/2013
|
1
|
Q
|
8
|
4/1/2013
|
2
|
C
|
10
|
4/2/2013
|
2
|
C
|
8
|
4/3/2013
|
2
|
C
|
8
|
4/4/2013
|
2
|
C
|
8
|
4/5/2013
|
2
|
Q
|
8
|
4/7/2013
|
2
|
C
|
8
|
4/8/2013
|
2
|
C
|
8
|
4/1/2013
|
3
|
Q
|
10
|
Below SQL Script is used to calculate each employee’s task
hours for Design, Coding, Functional Testing and QA by using SQL Pivot operator
and generate below tabular report:
To write the SQL Pivot, we need to perform
the two steps:
1.
Create
either sub-query or CTE which fetch records which is being pivoting.
2.
Apply
the PIVOT operator
Generated Tabular Report:
EMPOYEE ID
|
NAME
|
DESIGN
|
CODING
|
FUNCTIONAL TEST
|
QA
|
2
|
BILL
GATES
|
0
|
50
|
0
|
8
|
3
|
FRED
FLINTSTONE
|
0
|
0
|
0
|
10
|
1
|
SLOOPY
JOE
|
10
|
24
|
8
|
8
|
