Using Pivot In SQL for Reporting

Many times, we need to show some reports such as Department Wise Expense, or Subject wise marks of students where department and subject are stored in rows and we are to show all these as Columns. In all these situations, we can use SQL Pivoting.

For example, for Department Wise expense:

Suppose we store expenses as:

Dept ExDate       expense
_________________________
A   |  12 Jan |  10000
A   |  15 Jan |   9000
B   |  02 Feb |   8000
C   |  02 Feb |   8000
A   |  05 Feb |  10000
C   |  12 Feb |   8000

select ExDate, [A] as 'Store A Expense', [B] as 'Store B Expense', [C] as 'Store C Expense' from
(
select * from expenseTable
) ExpenseData
PIVOT
(
    sum(expense) for Dept in ([A],[B],[C]) 
)as FinalData

You can find one more detailed script here[^].

--Pankaj