Assuming I have a resulting data-set of SQL query by joining multiple tables as follows:
Class_Name Roll_No Student_Name Fee_Type Fee_Name Amount
---------- ------- ------------ ---------- ---------- ------
Class_1 101 abc Fee_Type_1 Fee_1 100
Class_1 101 abc Fee_Type_1 Fee_2 200
Class_1 101 abc Fee_Type_1 Fee_3 500
Class_1 101 abc Fee_Type_2 Fee_4 300
Class_1 101 abc Fee_Type_1 Fee_5 400
Class_1 102 xyz Fee_Type_1 Fee_1 100
Class_1 102 xyz Fee_Type_1 Fee_2 100
Class_1 102 xyz Fee_Type_2 Fee_4 300
Class_1 102 xyz Fee_Type_2 Fee_5 400
Class_2 201 mno Fee_Type_1 Fee_1 200
Class_2 201 mno Fee_Type_1 Fee_2 200
Class_2 201 mno Fee_Type_1 Fee_3 500
Class_2 201 mno Fee_Type_2 Fee_4 300
Class_2 202 pqr Fee_Type_1 Fee_1 200
Class_2 202 pqr Fee_Type_1 Fee_2 100
Class_2 202 pqr Fee_Type_1 Fee_3 500
Class_2 202 pqr Fee_Type_2 Fee_4 300
Class_2 202 pqr Fee_Type_2 Fee_5 600
The desired output is in following format
-----------------------------------------------------------------------------
| Fee_Type_1 | Fee_Type_2 | Student |
------------------------------------------------------------==============---------------============== Total |
Class_Name | Roll_No | Student_Name | Fee_1 | Fee_2 | Fee_3 | Type_Total | Fee_4 | Fee_5 | Type_Total | |
------------------------------------------------------------==============---------------==============----------
Class_1 | 101 | abc | 100 | 200 | 500 | 800 | 300 | 400 | 700 | 1500 |
| 102 | xyz | 100 | 100 | 0 | 200 | 300 | 300 | 600 | 800 |
-----------------------------------------------------------------------------------------------------------------
Class Total | 200 | 300 | 500 | 1000 | 600 | 700 | 1300 | 2300 |
-----------------------------------------------------------------------------------------------------------------
Class_2 | 201 | mno | 200 | 200 | 500 | 900 | 300 | 0 | 300 | 1200 |
| 202 | pqr | 200 | 100 | 500 | 800 | 300 | 600 | 900 | 1700 |
-----------------------------------------------------------------------------------------------------------------
Class Total | 400 | 300 | 1000 | 1700 | 600 | 600 | 1200 | 2900 |
-----------------------------------------------------------------------------------------------------------------
Fee Total | 600 | 600 | 1500 | 2700 | 1200 | 1300 | 2500 | 5200 |
-----------------------------------------------------------------------------------------------------------------
The resulting SQL query data-set is fully dynamic, therefore, column names can not be hard-coded.
Generating pivot tables through SQL or ASP.NET code and adding totals of row (Student Total) and each column and sub grouping (Fee Total and Class Total) is easily achievable as mentioned in above given resource links. The problem is to get the Sub Totals in each row (Type_Total column for every Fee_Name under Fee_Type (Fee_1 + Fee_2 + Fee_3 and Fee_4 + Fee_5) in given example).
How can these sub totals be added to dynamic data?
Tools: MS-SQL Server 2008 R2 and ASP.NET (.NET Framework 4.0). The desired solution can be SQL query, SQL stored procedure or C# / VB.NET back-end code.
References I have studied and implemented the following resources available:
ASP.NET references for dynamic pivot: (A) Pivoting-DataTable-Simplified Sub total of ABC Infotech, TPS Software etc. along with total of all columns for each row is desired and (B) Pivot-Grid-in-Asp-Net-MVC Total of Apparel, Electronics and Food Items along with total of all columns for each row is desired.
ASP.NET reference for group and grand total for columns: group-total-grand-total-in-grid-view
SQL references for dynamic SQL pivot: (A) t-sql-pivot-possibility-of-creating-table-columns-from-row-values and (B) dynamic-pivot-in-sql-server
PS: Any reporting software such as Crystal Reports or excel can not be used.
