Row and Column wise Total in pivot table

Question

Row and Column wise Total in pivot table

akhterhussain80

Mr or Mrs. 500

Points: 504
More actions
June 20, 2019 at 7:20 am

#3655633

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 '),
here is my store procedure
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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

Your problem is that you are using the PIVOT keyword for the pivot. That gives you slightly more compact code in the plain-vanilla case, but when you want something else like a column-level sum you are trapped in a straight-jacket.

A much more flexible way to write the pivot is as in this example:

SELECT E.LastName,
       [1996] = SUM(CASE Year(OrderDate) WHEN '1996' THEN 1 ELSE 0 END),
       [1997] = SUM(CASE Year(OrderDate) WHEN '1997' THEN 1 ELSE 0 END),
       [1998] = SUM(CASE Year(OrderDate) WHEN '1998' THEN 1 ELSE 0 END)
FROM   Orders O
JOIN   Employees E ON O.EmployeeID = E.EmployeeID
GROUP  BY E.LastName

This lists the number of orders per employee with one column per year. If you also want an overall total - I don't have to tell you how to achieve that.

You also want a total row. This can be achieved with the GROUPING SETS clause:

SELECT E.LastName,
       [1996] = SUM(CASE Year(OrderDate) WHEN '1996' THEN 1 ELSE 0 END),
       [1997] = SUM(CASE Year(OrderDate) WHEN '1997' THEN 1 ELSE 0 END),
       [1998] = SUM(CASE Year(OrderDate) WHEN '1998' THEN 1 ELSE 0 END)
FROM   Orders O
JOIN   Employees E ON O.EmployeeID = E.EmployeeID
GROUP  BY GROUPING SETS ((E.LastName), ())

As for how to package this into dynamic SQL, I leave that as an exercise to the reader. It is worth pointing out that the operation you are attempting is something which often is better performed in the presentation layer. Many reporting tools have support for dynamic pivots.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

akhterhussain80 Mr or Mrs. 500 Points: 504 More actions · Answer 2

thanks for your reply but i am still confuse that how it will use in my case.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

First build the statement with static SQL and forget the dynamic part, so that you understand how the generated query should look like. Once you have that in place, you can start thinking of building the dynamic SQL.

...or reconsider whether you should do the pivoting in SQL at all.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Please see the following articles. The first compares PIVOTs to an old "Black Arts" technique known a CROSSTAB (as well as fully explaining CROSSTABs), which is what Erland is talking about. The second demonstrates how to write DYNAMIC CROSSTABs and, again, it demonstrates the same technique of write code that works and convert that to dynamic that Erland spoke of.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)