Calculate Group wise Sum using Pivot function in SQL Server

Last Reply one month ago By dharmendr

Posted one month ago

I want sum of item qty month wise ...

here is my data ..

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
 
INSERT INTO #ItemMasterFile VALUES
  (1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);
 
INSERT INTO #Probale VALUES
   (1,1,1,001,100,'01-05-2019',null),
      (1,1,1,001,100,'01-06-2019',null)
, (2,3,1,001,200,'02-07-2019',null)
, (3,11,1,002,200,'03-08-2019',null)
, (4,10,1,002,200,'08-08-2019',null)
, (4,1,1,003,200,'08-08-2019',null)
, (4,1,1,003,200,'08-08-2019',null);

Expected result

S.No Item May-19 Jun-19 Jul-19 Aug-19
1 A 1 1 0 2
2 B 0 0 0 0
3 C 0 0 1 0
4 D 0 0 0 0
5 E 0 0 0 0
6 F 0 0 0 0
7 E 0 0 0 0
8 G 0 0 0 0
9 H 0 0 0 0
10 K 0 0 0 1
11 L 0 0 0 1
12 m 0 0 0 0
You are viewing reply posted by: dharmendr one month ago.