SQL Pivot function

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