65.9K
CodeProject is changing. Read more.
Home

Using Pivot In SQL for Reporting

starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.50/5 (3 votes)

Feb 28, 2011

CPOL
viewsIcon

20461

Nice and fast way 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