title: Dynamic pivot with sub column total
body: <p>Assuming I have a resulting data-set of SQL query by joining multiple tables as follows:  </p>
  
  <pre><code>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
  </code></pre>
  
  <p>The desired output is in following format</p>
  
  <pre><code>                                    -----------------------------------------------------------------------------
                                      |               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   |
  -----------------------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>The resulting SQL query data-set is fully dynamic, therefore, column names can not be hard-coded.  </p>
  
  <p>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. <strong>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).</strong>  </p>
  
  <p><strong>How can these sub totals be added to dynamic data?</strong>  </p>
  
  <p>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.  </p>
  
  <p><strong>References</strong>
  I have studied and implemented the following resources available:  </p>
  
  <ol>
  <li><p>ASP.NET references for dynamic pivot: (A) <a href="http://www.codeproject.com/Articles/46486/Pivoting-DataTable-Simplified" rel="nofollow noreferrer">Pivoting-DataTable-Simplified</a> Sub total of ABC Infotech, TPS Software etc. along with total of all columns for each row is desired and (B) <a href="http://www.codeproject.com/Articles/810267/Pivot-Grid-in-Asp-Net-MVC" rel="nofollow noreferrer">Pivot-Grid-in-Asp-Net-MVC</a> Total of Apparel, Electronics and Food Items along with total of all columns for each row is desired.  </p></li>
  <li><p>ASP.NET reference for group and grand total for columns: <a href="http://www.dotnettwitter.com/2011/02/group-total-grand-total-in-grid-view.html" rel="nofollow noreferrer">group-total-grand-total-in-grid-view</a>  </p></li>
  <li><p>SQL references for dynamic SQL pivot: (A) <a href="https://stackoverflow.com/questions/2922797/t-sql-pivot-possibility-of-creating-table-columns-from-row-values">t-sql-pivot-possibility-of-creating-table-columns-from-row-values</a> and (B) <a href="http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/" rel="nofollow noreferrer">dynamic-pivot-in-sql-server</a>  </p></li>
  </ol>
  
  <p>PS: Any reporting software such as Crystal Reports or excel can not be used.</p>