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

akhter

SOLVED CLOSED ACTIVE ANSWERED
User: akhter
Posted: on Jun 20, 2019 07:15 AM
Forum: SQL Server
Answer: 1
Views: 22799

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

My desired output

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')

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 1

Reply
ANSWER
User: dharmendr
Replied: on Jun 20, 2019 07:42 AM
Modified: on Jun 20, 2019 07:43 AM
Report

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

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

Company

Explore

Follow us

Contact Us