Hi dgunners,

Refer

Convert Rows To Columns and Calculate Sum using dynamic Pivot query in SQL Server

Dynamic Pivot query on multiple tables in SQL Server

Thanks @Shivam.M.

https://www.aspsnippets.com/questions/536555/Use-multiple-aggregate-columns-in-pivot-query-in-SQL-Server/

This link in one of the resources you suggested above gave the nearesd alterative output, but the manner of achieving was manual.

I am working with a very dynamic database such that the pivoted columns are all auto-generated, not manually because the output vary depending on what class student is in or what subjects they offer.

So how do I manage to create pivot with two more more agregated fields?

As per the expected output it is not possible in sql server or code behind as the columns are dynamic.

Also you can't achieve this in Excel. In excel you need manually set the header and merge the common columns in the header.

I will try it using ClosedXml library. Give us some time. We will get back to you tomorrow.

Hi dgunners,

Check below example. You need to add the cell value and merge the cells using ClosedXML library.

Refer below link for details on ClosedXML.

Export data from SQL Server to Excel in ASP.Net using C# and VB.Net

Check the sample and further modify as per your requirement.

Namespaces

using System.IO;
using System.Data;
using ClosedXML.Excel;

Code

protected void Page_Load(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] {
        new DataColumn("Stud-ID"),
        new DataColumn("NAME"),
        new DataColumn("TEST1"),
        new DataColumn("EXAM1"),
        new DataColumn("TOTAL1"),
        new DataColumn("TEST2"),
        new DataColumn("EXAM2"),
        new DataColumn("TOTAL2"),
        new DataColumn("OVERALLTOTAL") });
    dt.Rows.Add(1, "STUDENT-1", 20, 40, 60, 12, 20, 32, 92);
    dt.Rows.Add(2, "STUDENT-2", 30, 50, 80, 25, 45, 70, 150);
    dt.Rows.Add(3, "STUDENT-3", 15, 50, 65, 14, 64, 78, 143);
    using (XLWorkbook wb = new XLWorkbook())
    {
        // Add Worksheet to Excel. 
        var ws = wb.Worksheets.Add("Students");

        // Setting cell value.
        ws.Cell(1, 1).Value = "Stud-ID";
        ws.Cell(1, 2).Value = "NAME";
        ws.Cell(1, 3).Value = "ENGLISH";
        // Merge cell and center align.
        ws.Range(ws.Cell(1, 3), ws.Cell(1, 5)).Merge()
            .Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        ws.Cell(1, 6).Value = "MATHS";
        // Merge cell and center align.
        ws.Range(ws.Cell(1, 6), ws.Cell(1, 8)).Merge()
            .Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        ws.Cell(1, 9).Value = "OVERALL TOTAL";

        // Setting cell value for Header.
        ws.Cell(2, 1).Value = string.Empty;
        ws.Cell(2, 2).Value = string.Empty;
        ws.Cell(2, 3).Value = "TEST";
        ws.Cell(2, 4).Value = "EXAM";
        ws.Cell(2, 5).Value = "TOTAL";
        ws.Cell(2, 6).Value = "TEST";
        ws.Cell(2, 7).Value = "EXAM";
        ws.Cell(2, 8).Value = "TOTAL";
        ws.Cell(2, 9).Value = string.Empty;

        // Inserting Rows from DataTable.
        ws.Cell(3, 1).InsertData(dt.Rows);

        // Auto adjust column width.
        ws.Columns().AdjustToContents();

        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=Data.xlsx");
        using (MemoryStream ms = new MemoryStream())
        {
            wb.SaveAs(ms);
            ms.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

Screenshot