Using Pivot In SQL for Reporting






3.50/5 (3 votes)
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 FinalDataYou can find one more detailed script here[^]. --Pankaj