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   
;

Example :

EMP  Table :
It contains employee records
                                                                 

EMP_ID	EMP_NAME
0001	SLOOPY JOE
0002	BILL GATES
0003	FRED FLINTSTONE

TASK Table :
It is Task Lookup table

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
2013-04-01	0001	D	10
2013-04-02	0001	C	8
2013-04-03	0001	C	8
2013-04-04	0001	F	8
2013-04-05	0001	C	8
2013-04-07	0001	Q	8
2013-04-01	0002	C	10
2013-04-02	0002	C	8
2013-04-03	0002	C	8
2013-04-04	0002	C	8
2013-04-05	0002	Q	8
2013-04-07	0002	C	8
2013-04-08	0002	C	8
2013-04-01	0003	Q	10

Getting hours information as task wise for each employee

EMP_ID	NAME	DESIGN	CODING	FUNCTIONAL_TEST	QA
0002	BILL GATES	0	50	0	8
0003	FRED FLINTSTONE	0	0	0	10
0001	SLOOPY JOE	10	24	8	8

Here is SQL Pivot Script to calcuate each employee 's  task hours for Design, Coding , functional Testing and QA and generate above tabluar report

Select 
EMP_ID, 
EMP_NAME, 
ISNULL ([DESIGN],0) [DESIGN],
ISNULL([CODING],0) [CODING],
ISNULL([FUNCTION TEST],0) [FUNCTION TEST],
ISNULL([QA],0)[QA]

FROM

(
    SELECT 
    e.EMP_ID, 
    e.EMP_NAME, 
    t.TASK_DESC,
    h.HOURS

    FROM EMPLOYEEHOURS h

    inner join EMP e on h.EMP_ID  = e.EMP_ID

    inner join TASK t on h.TASK_CD = t.TASK_CD

) p

PIVOT

(

   sum (HOURS)

   FOR TASK_DESC IN

   ([DESIGN],[CODING],[FUNCTION TEST],[QA])

) AS pvt

ORDER BY 2;

Thanks for Visiting!!

Other SQL Links : 

Delete Duplicate record in SQL Server