Calculate Rows and columns Total in pivot table in SQL Server

Last Reply 4 months ago By akhter

Posted 4 months ago

I want this data in gridview

Date        Container(A)    Container(B)     Container(C)    Container(D)    Container(E)    Container(F)    Total
28/5/2019     200 200 200 300 100 200 1200
29/5/2019 100 100 50 50 50 50 400
30/5/2019 100 100 100  100 100 100 600
Total 400 400 350 450 250 350 2200

 

here is my table and data. 

create table #Containerno(CID int, ContNo varchar(50))

insert into #Containerno

select 1, 'Container(A)', union

select 2, ' Container(B)', 

select 3, ' Container(C)', 

select 4, ' Container(D)', 

select 5, ' Container(E)', 

select 6, ' Container(F)', 

create table #ConIssuance(IID int, CID  int,Iweight int,EntryDate date)

insert into #ConIssuance

select 1, '1', '200','28/5/2019', union

select 2, '2', '200','28/5/2019', 

select 3, '3', '200','28/5/2019', 

select 4, '4', '300','28/5/2019', 

select 5, '5, '100','28/5/2019', 

select 6, '6, '200','28/5/2019', 

select 7, '1, '100','29/5/2019', 

select 8, '2, '100','29/5/2019', 

select 9, '3, '50','29/5/2019', 

select 10, '4, '50','29/5/2019', 

select 11, '5, '50','29/5/2019', 

select 12, '6, '50','29/5/2019', 

 

Posted 4 months ago

here is sql code for pivot table row and column total in gridview between two table with date parameter

 

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:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER Procedure [dbo].[Rags_itemwise]
@StartDate Date,
@Enddate   Date

AS
BEGIN
declare @cols nvarchar(max);

SELECT  @cols= COALESCE(@cols + ',','') + QUOTENAME(contNo) from containerno;
declare @query nvarchar(max);

declare @sum nvarchar(max);

SELECT  @sum= COALESCE(@sum + ',','') + ('sum('+QUOTENAME(contNo)+')') from containerno;

--declare  @sum nvarchar(max);

--select @sum =

SET @query =

N' 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 (' + @cols + ') )AS stat)

select * from cte

union all
select 0,'+@sum+', (select sum(iweight ) from conissuance ) from  cte

'
exec sp_executesql @query, N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate
end