Calculate Row Total and Column Total using dynamic Pivot query in SQL Server

Last Reply one year ago By dharmendr

Posted one year ago

I want row and column wise total , giving error after executing store procedure 

My desired output

https://ibb.co/sHBcffh

ALTER Procedure [dbo].[Pivot_Item2]
@StartDate Date,
@Enddate   Date

AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
declare @sum nvarchar(max);
SELECT  @sum= COALESCE(@sum + ',','') + ('sum('+QUOTENAME(prdqty)+')') from Probale;
	-- 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(10),EntryDate) )
--FROM  (SELECT DISTINCT  DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
FROM (SELECT DISTINCT EntryDate AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate 

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

SET @query =
N'with cte as (SELECT *,(select sum(prdqty) from Probale where  CONVERT(varchar(10), EntryDate) =stat.ddate ) as total
FROM (SELECT Descriptionitem,Probale.prdqty,

CONVERT(varchar(10),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)

select * from cte

union all
select 0,'+@sum+', (select sum(prdqty ) from Probale ) from  cte'
exec  sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate
END

You may used there table and data

CREATE TABLE #Catagory (CID INT,CName VARCHAR(50))
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50))
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,CID INT)
CREATE TABLE #Probale(BID INT,CodeItem INT,SecID INT,CID INT,prdqty INT,Entrydate DATETIME)
 
INSERT INTO #Catagory VALUES(1,'INDIA')
INSERT INTO #Catagory VALUES(2,'INDIA(Cut)')
INSERT INTO #Catagory VALUES(3,'Pakistan')
INSERT INTO #Catagory VALUES(4,'Pakistan(Cut)')
 
 
INSERT INTO #Sections VALUES(1,'HR')
INSERT INTO #Sections VALUES(2,'Baby')
INSERT INTO #Sections VALUES(3,'Ladies')
INSERT INTO #Sections VALUES(4,'Mix Rammage')

INSERT INTO #ItemMasterFile VALUES(1,'A',1,1)
INSERT INTO #ItemMasterFile VALUES(2,'B',2,2)
INSERT INTO #ItemMasterFile VALUES(3,'C',3,3)
INSERT INTO #ItemMasterFile VALUES(4,'D',4,null)
INSERT INTO #ItemMasterFile VALUES(5,'e',5,null)
INSERT INTO #ItemMasterFile VALUES(6,'f',6,null)
INSERT INTO #ItemMasterFile VALUES(7,'g',4,2)
INSERT INTO #ItemMasterFile VALUES(8,'h',4,3)
INSERT INTO #ItemMasterFile VALUES(9,'K',2,2)
 
INSERT INTO #Probale VALUES(1,1,1,1,1,'01-06-2019')
INSERT INTO #Probale VALUES(2,3,3,3,1,'02-06-2019')
INSERT INTO #Probale  VALUES(3,4,4,null,1,'03-06-2019')
INSERT INTO #Probale VALUES(4,4,4,null,1,'04-06-2019')
You are viewing reply posted by: dharmendr one year ago.
Posted one year ago Modified on one year ago

Hi akhter,

Please check the below query and modify your query according to your table structure.

SQL

CREATE TABLE #FOOD(Id INT, NAME VARCHAR(10))
INSERT INTO #FOOD(Id, NAME)
SELECT 1,'apple' UNION ALL SELECT 2,'orange' UNION ALL SELECT 3,'tomato'
   
CREATE TABLE #PEOPLE(Id INT, NAME VARCHAR(10))
INSERT INTO #PEOPLE(Id, NAME)
SELECT 1,'Imomdod' UNION ALL SELECT 2,'Muhammad' UNION ALL SELECT 3,'Alisher'
    
CREATE TABLE #SALES(Id INT, IdPer INT, IDFOOD INT, price float)
INSERT INTO #SALES(Id, IdPer, IDFOOD, price)
SELECT 1,2,1, 3 UNION ALL SELECT 2,3,1, 5 UNION ALL SELECT 3,1,3, 11 UNION ALL
SELECT 4,3,2, 2 UNION ALL SELECT 5,2,1, 5 UNION ALL SELECT 6,1,2, 8 UNION ALL
SELECT 7,2,3, 4 UNION ALL SELECT 8,3,2, 10 UNION ALL SELECT 9,2,3, 15 UNION ALL
SELECT 10,1,2, 18 UNION ALL SELECT 11,3,3, 13 UNION ALL SELECT 12,1,3, 19
 
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(NAME) FROM (SELECT DISTINCT [NAME] FROM #FOOD) AS [NAME]
DECLARE @ColumnForSum AS NVARCHAR(MAX)
SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
 
DECLARE @RowForSum AS NVARCHAR(MAX)
SELECT @RowForSum = REPLACE(@ColumnName,',',',')
SELECT @RowForSum = REPLACE(@RowForSum,'[','SUM(ISNULL([')
SELECT @RowForSum = REPLACE(@RowForSum,']','],0))')
 
SET @DynamicPivotQuery =
';WITH cte
AS (
    SELECT * FROM
    (
        SELECT PersonName,'+@ColumnName+'
        ,SUM('+@ColumnForSum+') Summa
        FROM (
                SELECT SUM(s.Price) Price,p.NAME as PersonName,f.NAME as FoodName
                from #SALES s
                INNER JOIN #PEOPLE p ON s.IdPer = p.Id
                INNER JOIN #FOOD f ON s.IDFOOD = f.Id
                GROUP BY p.Name,f.Name
            )t
        pivot
        (
            MAX([Price]) FOR FoodName IN ('+ @ColumnName +')
        ) piv GROUP BY PersonName,'+ @ColumnName +'
    ) r
)
SELECT PersonName,'+ @ColumnName +',Summa FROM cte
UNION
SELECT ''Total'','+ @RowForSum +',SUM(Summa) FROM cte'
EXEC (@DynamicPivotQuery)
  
DROP TABLE #SALES
DROP TABLE #FOOD
DROP TABLE #PEOPLE

Output

PersonName apple orange tomato Summa
Alisher 5 12 13 30
Imomdod NULL 26 30 56
Muhammad 8 NULL 19 27
Total 13 38 62 113