Fetch data Month Wise using Dynamic PIVOT query in SQL Server

Last Reply 3 months ago By pandeyism

Posted 3 months ago

I want to display data in month here is my data and expected result

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
Posted 3 months ago

Hi akhter,

Refer below sample query.

SQL

CREATE TABLE #ItemMasterFile(ItemCode INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,ItemCode INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);

INSERT INTO #ItemMasterFile VALUES(1,'A',1,100)
INSERT INTO #ItemMasterFile VALUES(2,'B',2,100)
INSERT INTO #ItemMasterFile VALUES(3,'C',3,100)
INSERT INTO #ItemMasterFile VALUES(4,'D',4,100)
INSERT INTO #ItemMasterFile VALUES(5,'e',5,100)
INSERT INTO #ItemMasterFile VALUES(6,'f',6,100)
INSERT INTO #ItemMasterFile VALUES(7,'g',4,100)
INSERT INTO #ItemMasterFile VALUES(8,'h',4,100)
INSERT INTO #ItemMasterFile VALUES(9,'K',2,100)
INSERT INTO #ItemMasterFile VALUES(10,'L',4,100)
INSERT INTO #ItemMasterFile VALUES(11,'M',2,100);

INSERT INTO #Probale VALUES(1,1,1,001,100,'01-05-2019',null)
INSERT INTO #Probale VALUES(1,1,1,001,100,'01-06-2019',null)
INSERT INTO #Probale VALUES(2,3,1,001,200,'02-07-2019',null)
INSERT INTO #Probale VALUES(3,11,1,002,200,'03-08-2019',null)
INSERT INTO #Probale VALUES(4,10,1,002,200,'08-08-2019',null)
INSERT INTO #Probale VALUES(4,1,1,003,200,'08-08-2019',null)
INSERT INTO #Probale VALUES(4,1,1,003,200,'08-08-2019',null)

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)      
SELECT @ColumnName= ISNULL(@ColumnName + ',','')+ QUOTENAME(Entrydate)
FROM (SELECT DISTINCT Entrydate FROM #Probale) AS Entrydate
SET @DynamicPivotQuery =
  N'SELECT *
    FROM (
          SELECT t.ItemCode,t.Descriptionitem,t2.prdQTY,t2.Entrydate
          FROM #ItemMasterFile t
         FULL OUTER JOIN #Probale t2
      ON t.ItemCode = t2.ItemCode
          ) p
  PIVOT(Max(prdQTY) FOR Entrydate IN (' +@ColumnName+ ')) AS pvt'
EXEC (@DynamicPivotQuery)
 
DROP TABLE #ItemMasterFile
DROP TABLE #Probale