Order Wise Item Produce Data require in SQL.?

Akhter HUssain

7y954

here is my data

CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,);
CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50));
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);
CREATE TABLE #Emp_Strength(EID INT,SecID INT,QTY int, Entrydate DATETIME);
INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');
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-06-2019',null)
, (2,3,1,001,200,'02-06-2019',null)
, (3,11,1,002,200,'03-06-2019',null)
, (4,10,1,002,200,'08-06-2019',null)
, (4,1,1,003,200,'08-06-2019',null)
, (4,3,1,003,200,'08-06-2019',null);
INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019')
, (002,'B_01','01-05-2019')
, (003,'C_01','01-05-2019');
INSERT INTO #OrderDetail VALUES
(1,001,1,5)
, (2,001,2,3)
, (3,001,3,2)
, (4,002,10,4)
, (5,002,11,3)
, (6,002,3,2)
, (7,003,1,4)
, (8,003,2,3)
, (9,003,3,2);

and require this output

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka Jain7y ago
Accepted answer
As per your query you need to use pivot table , below is the query
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @sqlQuery AS NVARCHAR(MAX);

SET @SqlQuery = 'SELECT DISTINCT sec.Secnam ,s.Order_Ref_No,SUM(p.prdqty) AS ProductQty
                 FROM #ItemMasterFile i
                 INNER JOIN #Probale p ON i.CodeItem = p.CodeItem
                 INNER JOIN #SalesOrder s on p.OrderNo = s.OrderNO
                 INNER JOIN #Sections sec on i.SecID = sec.SecID
                 GROUP BY sec.Secnam,s.Order_Ref_No
                 ';

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.Order_Ref_No)
            FROM (SELECT s.Order_Ref_No FROM
                         #SalesOrder s GROUP BY s.Order_Ref_No) c
                  FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)')
            ,1,1,'')

DECLARE @colList AS NVARCHAR(MAX),@colsWithoutNull AS NVARCHAR(MAX)

SET @colsWithoutNull = STUFF((SELECT DISTINCT ',ISNULL(' + QUOTENAME(c.Order_Ref_No) + ',0) as [' + c.Order_Ref_No + ']'
            FROM (SELECT s.Order_Ref_No FROM
                         #SalesOrder s GROUP BY s.Order_Ref_No) c
                  FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)')
            ,1,1,'')

SET @colList = STUFF((SELECT DISTINCT '+ISNULL(' + QUOTENAME(c.Order_Ref_No) + ',0)'
            FROM (SELECT s.Order_Ref_No FROM
                         #SalesOrder s GROUP BY s.Order_Ref_No) c
                  FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)')
            ,1,1,'')

SET @query = 'SELECT Secnam As DescriptionItem,' + @colsWithoutNull + ', '+ @colList + ' AS Total FROM
            (
                '+@sqlQuery+'
           ) x
            pivot
            (
                 SUM(ProductQty)
                for Order_Ref_No in (' + @cols + ')
            ) p'

EXECUTE(@query)
This will display output as
+2
Priyanka Jain7y ago
Updated query changes count to sum funtion and added 0 incase of null value with latest snap
+1
Akhter HUssain7y ago
my result coming is different with same data and your provided query

0