Inserting subtotals to my dynamically generated DataTable-CodePudding

I have a SQL Server temp table in my .Net core application created using Dapper. I then pivot this, generating dynamically a DataTable, which I then use to export to Excel for users.

What I am trying to add to this is subtotal rows, but have been unable to work it out. Here is a simple demo representation of my code and what I am hoping to achieve:

IF OBJECT_ID('tempdb..#t2') IS NOT NULL
    DROP TABLE #t2

create table #t2 (
                    Client varchar(50), 
                    TradeId varchar(50),
                    BusDate datetime,
                    ExpiryDate datetime,
                    Amount int
)

insert into #t2 values ('clientA', 'A0124', '20211110', '20211113', -400)
insert into #t2 values ('clientA', 'A0124', '20211111', '20211113', -400)
insert into #t2 values ('clientA', 'A0124', '20211112', '20211113', -400)
insert into #t2 values ('clientA', 'A0124', '20211113', '20211113', -400)
insert into #t2 values ('clientA', 'A0125', '20211110', '20211113', 250)
insert into #t2 values ('clientA', 'A0125', '20211111', '20211113', 250)
insert into #t2 values ('clientA', 'A0125', '20211112', '20211113', 250)
insert into #t2 values ('clientA', 'A0125', '20211113', '20211113', 250)
insert into #t2 values ('clientB', 'B0125', '20211110', '20211112', 100)
insert into #t2 values ('clientB', 'B0125', '20211111', '20211112', 100)
insert into #t2 values ('clientB', 'B0125', '20211112', '20211112', 100)
insert into #t2 values ('clientC', 'C0125', '20211110', '20211111', -500)
insert into #t2 values ('clientC', 'C0125', '20211111', '20211111', -500)

Results:

Next stage shows my pivot for presentation of the data to users:

-- pivot to create dynamic datatable for further exporting to Excel 

DECLARE @cols2 AS NVARCHAR(MAX),
    @query2  AS NVARCHAR(MAX)



select @cols2 = STUFF((SELECT ','   QUOTENAME(CobDate) 
                    from #t2
                    group by CobDate
                    order by CobDate asc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query2 = N'SELECT CPartyName, SourceSystemTradeID, '   @cols2   N' from 
                (
                select CpartyName, SourceSystemTradeID, CobDate, TradeMaturityDate, NotionalFX
                from #t2
                                    
            ) x
            pivot 
            (
                max(NotionalFX) 
                for CobDate in ('   @cols2   N')
            ) p '

            exec sp_executesql @query2;

Results:

So I'm trying to insert a subtotal row under each clients' rows, for example in this demo this row would be below the two clientA rows:

I have tried adding this logic into the pivot query in sql, also have attempted to amend the DataTable with the below incorrect C# method, but not working it out:

    private void InsertSubTotals(ref DataTable dt)
    {
        DataRow totalsRow = dt.NewRow();
        foreach (DataColumn col in dt.Columns)
        {
            if (col.ColumnName != "Client" && col.ColumnName != "TradeId")
            {
                var colTotal = 0;
                foreach (DataRow row in col.Table.Rows)
                {
                    var t = int.Parse(row[col].ToString());
                    colTotal  = t;
                }
                totalsRow[col.ColumnName] = colTotal;
            }

        }
        dt.Rows.Add(totalsRow);

    }

Any assistance much appreciated.

CodePudding user response：

I am not sure if I am following what the table looks like. However for any column, if it is a numeric column, then looping through the rows may be unnecessary. The DataTable has a Compute method that will SUM the column for you. Therefore you could alter the code to something like…

private void InsertSubTotals(DataTable dt) {
  DataRow totalsRow = dt.NewRow();
  totalsRow["ColumnName1"] = Convert.ToInt32(table.Compute("SUM(ColumnName1)", ""));
  totalsRow["ColumnName2"] = Convert.ToInt32(table.Compute("SUM(ColumnName2)", ""));
  //....
  dt.Rows.Add(totalsRow);
}

Granted, if there are many columns to sum, then you may want to use a loop.

Also it should be noted, that the second parameter in the Compute function is a “filter.” When empty, it sums all the cells in the column. However, you can also “filter” the sum to apply only to certain cells. Example the sum for only “clientA” clients may look something like…

totalsRow["ColumnName1"] = Convert.ToInt32(table.Compute("SUM(ColumnName1)", "Client = ‘clientA’"));

CodePudding user response：

This kind of thing is much easier with explicit conditional aggregation, rather than using the PIVOT syntax.

Then you can use GROUPING SETS to specify the exact rollup you want.

DECLARE @cols2 AS NVARCHAR(MAX) = (
    SELECT DISTINCT
      ', '   QUOTENAME(CobDate)   ' = MAX(CASE WHEN CobDate = '   QUOTENAME(CobDate, '''')   ' THEN NotionalFX END)'
    from #t2
    group by CobDate
    order by CobDate asc
    FOR XML PATH(''), TYPE
).value('text()[1]', 'NVARCHAR(MAX)');

DECLARE @query2 AS NVARCHAR(MAX) = N'
SELECT
  CPartyName,
  SourceSystemTradeID
'   @cols2   N'
from #t2
GROUP BY GROUPING SETS (
  (CpartyName, SourceSystemTradeID),
  (CpartyName)
);
';

PRINT @query2; --for testing

exec sp_executesql @query2;

I would say though, that it is much easier to do this kind of thing in Excel. SQL Server likes its columns to have fixed names.