SQLTeam.com | Weblogs | Forums

Row and Column wise Total in Pivot table

I want row wise and column wise Total

Create table #Sections (SecID int, Secnam varchar(50))
create table #ItemMasterFile(CodeItem int, Descriptionitem varchar(max),SecID int,CID int)
create table #Probale (BID int, CodeItem int, prdqtyint,EntryDate date)

insert into #Sections
values
(1, 'HR'),
(2, 'Baby'),
(3, 'Ladies'),
(4, 'Mix Rammage'),

insert into #ItemMasterFile
values
(1, 'A', '1'),
(2, 'B', '2'),
(3, 'C', '3'),
(4, 'D' ,'4')

insert into #Probale
values
(1, '1', '1', '5/01/2019 '),
(2, '2', '1', '5/01/2019 '),
(3, '3', '1', '5/01/2019 '),
(4, '2', '1', '5/02/2019 '),
(5, '3', '1', '5/02/2019 '),

(6, '4', '1', '5/02/2019 '),

(6, '1', '1', '5/03/2019 '),

(7, '2', '1', '5/04/2019 '),

(8, '4', '1', '5/05/2019 '),

Item Name 01/05/2019(Vertical) 02/05/2019 03/05/2019 4/05/2019 5/05/2019 Total
A 1 0 1 0 0 2
B 1 1 0 1 0 3
C 1 1 0 0 0 2
D 0 1 0 0 1 2
Total 3 3 1 1 1 9

here is my store procedure in which data is getting display but i want to display row and column wise total.

**ALTER Procedure [dbo].[Pivot_Item1] @StartDate Date,
@Enddate Date

AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

SELECT Distinct Entrydate INTO #Dates26 FROM Probale WHERE EntryDate BETWEEN @StartDate AND @Enddate ORDER BY EntryDate

SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(2),EntryDate) )
FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate

--SET @cols = STUFF(@cols, 1, 1, '')

SET @query =
N'SELECT *
FROM (SELECT Descriptionitem,Probale.prdqty,
DATEPART(DAY, Probale.EntryDate)as DDate
FROM Probale left JOIN ItemMasterFile on ItemMasterFile.Codeitem=Probale.Codeitem
where Probale.DelID is Null and Probale.EntryDate BETWEEN @StartDate AND @Enddate )prd
PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat'
exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate

END**

you sample data and DDL don't work

Display or hide grand totals
1.Click anywhere in the PivotTable.

2.On the Design tab, in the Layout group, click Grand Totals, and then select the grand total display option that you want.