ASPForums.Net RSS Feed additions to the content that appears on ASPForums.Net(c) 2019 All rights reserved.Calculate Rows and columns Total in pivot table in SQL Server<p>here is sql code for pivot table row and column total in gridview between two table with date parameter</p> <p>&nbsp;</p> <pre class="brush: sql">USE [SilverProduction] GO /****** Object: StoredProcedure [dbo].[Rags_itemwise] Script Date: 05/31/2019 17:18:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: &lt;Author,,Name&gt; -- Create date: &lt;Create Date,,&gt; -- Description: &lt;Description,,&gt; -- ============================================= ALTER Procedure [dbo].[Rags_itemwise] @StartDate Date, @Enddate Date AS BEGIN declare @cols nvarchar(max); SELECT @cols= COALESCE(@cols + &#39;,&#39;,&#39;&#39;) + QUOTENAME(contNo) from containerno; declare @query nvarchar(max); declare @sum nvarchar(max); SELECT @sum= COALESCE(@sum + &#39;,&#39;,&#39;&#39;) + (&#39;sum(&#39;+QUOTENAME(contNo)+&#39;)&#39;) from containerno; --declare @sum nvarchar(max); --select @sum = SET @query = N&#39; with cte as (SELECT *,(select sum(Iweight) from conissuance where DATEPART(DAY, EntryDate) =stat.ddate ) as total FROM (SELECT ContNo,ConIssuance.IWeight, DATEPART(DAY, ConIssuance.EntryDate)as DDate FROM ConIssuance inner JOIN Containerno on Containerno.CID=ConIssuance.CID where ConIssuance.EntryDate BETWEEN @StartDate AND @Enddate )prd PIVOT (Sum(IWeight) FOR ContNo IN (&#39; + @cols + &#39;) )AS stat) select * from cte union all select 0,&#39;+@sum+&#39;, (select sum(iweight ) from conissuance ) from cte &#39; exec sp_executesql @query, N&#39;@StartDate datetime,@enddate datetime&#39;, @StartDate=@StartDate,@Enddate=@Enddate end</pre> <p>&nbsp;</p>, 01 Jun 2019 00:45:11 GMT