how to display Grand Totals.

Hello Ramco,

To display a Grand Total row at the bottom, you can use GROUPING SETS or a ROLLUP approach. However, since you are using Dynamic SQL with PIVOT, you need to modify the query to union the total row.

-- Modified Query with Grand Total Row

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- Step 1: Generate column names dynamically
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(T2."U_A_M")
                   FROM Opch T0 
                   INNER JOIN Pch1 T1 ON T0."Docentry" = T1."DocEntry"
                   INNER JOIN Oitm T2 ON T1."ItemCode" = T2."ItemCode"
                   WHERE T2.U_A_M <> 'NA'
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- Step 2: Construct Dynamic SQL with Grand Total Row
SET @query = '
WITH PivotData AS (
    SELECT 
        T1."ItemCode",
        T1."LineTotal", 
        T2."U_A_M"
    FROM Opch T0 
    INNER JOIN Pch1 T1 ON T0."Docentry" = T1."DocEntry"
    INNER JOIN Oitm T2 ON T1."ItemCode" = T2."ItemCode"
    WHERE T2."U_A_M" <> ''NA''
)
SELECT * FROM (
    SELECT ItemCode, ' + @cols + ',
           (ISNULL(' + REPLACE(@cols, ',', ') + ISNULL(') + ', 0)) AS GrandTotal
    FROM (
        SELECT ItemCode, LineTotal, U_A_M 
        FROM PivotData
    ) AS SourceTable
    PIVOT (
        SUM(LineTotal)
        FOR U_A_M IN (' + @cols + ')
    ) AS PivotTable

    UNION ALL

    SELECT ''TOTAL'' AS ItemCode, ' + 
    REPLACE(@cols, ',', ' = SUM(') + ' = SUM(' + @cols + '), 
           SUM(ISNULL(' + REPLACE(@cols, ',', '), 0)) AS GrandTotal
    FROM (
        SELECT ItemCode, LineTotal, U_A_M 
        FROM PivotData
    ) AS SourceTable
    PIVOT (
        SUM(LineTotal)
        FOR U_A_M IN (' + @cols + ')
    ) AS PivotTable
) AS FinalResult
ORDER BY 
    CASE WHEN ItemCode = ''TOTAL'' THEN 1 ELSE 0 END, ItemCode;';

-- Step 3: Execute the Dynamic SQL
EXEC sp_executesql @query;

Good Luck!

Hello Ramco,

You can add grand total using below query,

DECLARE @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX);


SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(T2."U_A_M")
                   FROM Opch T0 
                   INNER JOIN Pch1 T1 ON T0."Docentry" = T1."DocEntry"
                   INNER JOIN Oitm T2 ON T1."ItemCode" = T2."ItemCode"
                   WHERE T2.U_A_M <> 'NA'
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
                   1, 1, '');


SET @query = '
SELECT *
FROM (
    SELECT itemcode, ' + @cols + ',
           COALESCE(' + REPLACE(@cols, ',', ' , 0) + ') AS GrandTotal
    FROM (
        SELECT T1."ItemCode", T1."LineTotal", T2."U_A_M"
        FROM Opch T0
        INNER JOIN Pch1 T1 ON T0."Docentry" = T1."DocEntry"
        INNER JOIN Oitm T2 ON T1."ItemCode" = T2."ItemCode"
        WHERE T2.U_A_M <> ''NA''
    ) x
    PIVOT (
        SUM(LineTotal) 
        FOR U_A_M IN (' + @cols + ')
    ) p
) final_table

UNION ALL

SELECT ''Total'', ' + REPLACE(@cols, ',', ' , SUM(') + ') , SUM(GrandTotal)
FROM (
    SELECT itemcode, ' + @cols + ',
           COALESCE(' + REPLACE(@cols, ',', ' , 0) + ') AS GrandTotal
    FROM (
        SELECT T1."ItemCode", T1."LineTotal", T2."U_A_M"
        FROM Opch T0
        INNER JOIN Pch1 T1 ON T0."Docentry" = T1."DocEntry"
        INNER JOIN Oitm T2 ON T1."ItemCode" = T2."ItemCode"
        WHERE T2.U_A_M <> ''NA''
    ) x
    PIVOT (
        SUM(LineTotal) 
        FOR U_A_M IN (' + @cols + ')
    ) p
) final_table;';

-- Execute the dynamic SQL query
EXECUTE(@query);

To display Grand Totals in your SQL query output, you can modify your existing query by adding a computation for the Grand Total. Here's how you can do it:

1. Add a computation in your SQL query that calculates the sum of each row to get the Grand Total.

2. Make sure you include this sum in your final query result set along with the individual columns.

Below is an example of how you can update your query to display the Grand Total:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(T2."U_A_M")
            FROM Opch T0 inner join Pch1 T1 on T0."Docentry" = T1."DocEntry"
	inner join Oitm T2 on T1."ItemCode" = T2."ItemCode"
	where T2.U_A_M <> 'NA'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT itemcode, ' + @cols + ', 
            ISNULL(SUM(' + @cols + '), 0) AS GrandTotal
            from
            (
                SELECT T1."ItemCode",T1."LineTotal", T2."U_A_M"
    FROM Opch T0 inner join Pch1 T1 on T0."Docentry" = T1."DocEntry"
	inner join Oitm T2 on T1."ItemCode" = T2."ItemCode"
	where T2.U_A_M <> ''NA''
           ) x
            pivot
            (
                 sum(linetotal)
                for U_A_M in (' + @cols + ')
            ) p '


execute(@query)

By adding `ISNULL(SUM(' + @cols + '), 0) AS GrandTotal` to your SELECT statement inside the dynamic SQL query, you will get the Grand Total for each row in the result set. This way, you can see both the individual totals for Admin, Purchase, and Sales columns along with the Grand Total for each item.

I hope this helps you in displaying the Grand Totals effectively in your SQL query output. Let me know if you have any further questions or need additional clarification!