locked
Row wise Total in Pivot table ,coming wrong RRS feed

  • 問題

  • i am trying to calculating total row wise in Pivot,which is coming wrong.

    CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
    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);
    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,'2020-01-13',null)
    , (2,3,1,001,200,'2020-01-15',null)
    , (3,11,1,002,200,'2020-01-15',null)
    , (5,10,1,002,200,'2020-01-16',null)
    , (6,1,1,003,200,'2020-01-16',null)
    , (7,3,1,003,200,'2020-01-17',null);
    
    INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
    , (002,'B_01','01-05-2019','Open')
    , (003,'C_01','01-05-2019','Open');
    
    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);
    
    
    
    
    declare @fromdate date='2020-01-13'
    declare @todate date='2020-01-15'
    declare @columns varchar(max)
    declare @convert varchar(max)
    declare @columns1 varchar(max)
    
    
    
    
    select   @columns = stuff (( select distinct'],[' +  Cast(S.Order_Ref_No AS Varchar(10))
    from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
     and P.DelID is null for xml path('')), 1, 2, '') + ']'
    
    select   @columns1 = (SELECT  distinct ',ISNULL([' + Cast(S.Order_Ref_No AS Varchar(10))+'],0)'  +  ' ['+Cast(S.Order_Ref_No AS Varchar(10))+']'
     from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
     and P.DelID is null   for xml path(''))
    
     set @columns1=SUBSTRING(@columns1,2,len(@columns1)-1)
    
    
     /* GRAND TOTAL COLUMN */
    DECLARE @GrandTotalCol	NVARCHAR (MAX)
    SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (S.Order_Ref_No AS Varchar(10)) +'],0) + ', 'ISNULL([' + CAST(S.Order_Ref_No AS Varchar(10))+ '],0) + ')
     from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate   and P.DelID is null 
    SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)
    
    set @convert =
    ('select codeitem, '+ @columns1+','+ @GrandTotalCol + ' from (select S.Order_Ref_No,P.codeitem,P.prdqty from #Probale P inner join #salesorder S on S.OrderNo=P.orderno 
    	where  P.Entrydate between ''' + CONVERT(VARCHAR(20),@fromdate,121) + ''' and ''' + CONVERT(VARCHAR(20), @todate, 121) + '''
    	and P.DelID is null
    
     ) smallbale
        pivot(sum(prdqty) for Order_Ref_No
        in ('+@columns+')) as pivottable')
    
    execute (@convert)
    
    
    drop table #SalesOrder
    drop table #OrderDetail
    drop table #Sections
    drop table #ItemMasterFile
    drop table #Probale


    akhter

    2020年8月14日 上午 04:18

解答

  • Also, it's is not a nice idea to change requirements or code and not even try to make the correct changes yourself:

    CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
    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);
    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,'2020-01-13',null)
    , (2,3,1,001,200,'2020-01-15',null)
    , (3,11,1,002,200,'2020-01-15',null)
    , (5,10,1,002,200,'2020-01-16',null)
    , (6,1,1,003,200,'2020-01-16',null)
    , (7,3,1,003,200,'2020-01-17',null);
    
    INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
    , (002,'B_01','01-05-2019','Open')
    , (003,'C_01','01-05-2019','Open');
    
    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);
    
    
    
    ---------------------
    declare @fromDate date='2020-01-13';
    declare @toDate date='2020-01-15';
    
    declare @pvtColumns nvarchar(max), @sqlQuery nvarchar(max), @qryColumns nvarchar(max), @totalsExpression nvarchar(max);
    
    drop table if exists #TempResults;
    
    SELECT p.prdqty,s.order_ref_no, i.Descriptionitem 
    into #TempResults
    from #Probale P 
    inner join #salesorder S on S.OrderNo=P.orderno 
    inner join #itemmasterfile i on i.codeitem=p.codeitem
    where P.Entrydate between @fromdate and  @todate
     and P.DelID is null;
    
    select @pvtColumns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(50)))
    from #TempResults
     for xml path('')), 1, 2, ''); 
    
    select @qryColumns = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0) AS ' + 
    quotename(Cast(Order_Ref_No AS Varchar(50)))
     from #TempResults 
      for xml path('')), 1, 2, ''); 
    
    select @totalsExpression = STUFF((SELECT  distinct ' + ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0)' 
     from #TempResults 
      for xml path('')), 1, 3, '');
    
    
    
    
    set @sqlQuery =
    'select ISNULL(DescriptionItem, ''Total:'')  as DescriptionItem, '+ @qryColumns +' , '+ @totalsExpression + ' as [Row Total] from 
    
    (select DescriptionItem, sum(prdQty) as Qty, Order_Ref_No from #TempResults
    GROUP BY order_Ref_No, ROLLUP(DescriptionItem)) sm
    pivot(sum(Qty) for Order_Ref_No
        in (' + @pvtColumns + ')) as pivottable
    order by case when PivotTable.DescriptionItem IS NULL then 1 else 0 end, DescriptionItem';
    
    --print @sqlQuery;
    
    execute (@sqlQuery)
    
    drop table #SalesOrder
    drop table #OrderDetail
    drop table #Sections
    drop table #ItemMasterFile
    drop table #Probale
    drop table if exists #TempResults;


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2020年8月19日 上午 05:33

所有回覆

  • Well, I see the problem in your code, but if you want to see it yourself and also being able to debug it, start from 

    PRINT @Convert 

    instead of EXECUTE (@Convert)

    ---------------------------------------

    Hint - why did you repeat the same expression twice in your GrandTotal variable?

    Also, I suggest to use QUOTENAME function - it will make your code more readable. And use the same approach you use for COLUMNS (e.g. use XML PATH('') to get the value concatenated).


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • 已編輯 Naomi N 2020年8月14日 上午 04:52
    2020年8月14日 上午 04:52
  • HI Naomi,

    what does it mean?

    Hint - why did you repeat the same expression twice in your GrandTotal variable?

    i am getting below on Print 

    (20 row(s) affected)
    
    (11 row(s) affected)
    
    (6 row(s) affected)
    
    (3 row(s) affected)
    
    (9 row(s) affected)
    select codeitem, ISNULL([A_01],0) [A_01],ISNULL([B_01],0) [B_01],ISNULL([A_01],0) + ISNULL ([A_01],0) + ISNULL ([B_01],0)  from (select S.Order_Ref_No,P.codeitem,P.prdqty from #Probale P inner join #salesorder S on S.OrderNo=P.orderno 
    	where  P.Entrydate between '2020-01-13' and '2020-01-15'
    	and P.DelID is null
    
     ) smallbale
        pivot(sum(prdqty) for Order_Ref_No
        in ([A_01],[B_01])) as pivottable
    


    akhter


    • 已編輯 Akhterhussain 2020年8月14日 上午 05:40 more addition
    2020年8月14日 上午 05:38
  • Hi Akhterhussain,

    Please try:

    declare @fromdate date='2020-01-13'
    declare @todate date='2020-01-15'
    declare @columns varchar(max)
    declare @convert varchar(max)
    declare @columns1 varchar(max)
    
    
    
    
    select   @columns = stuff (( select distinct'],[' +  Cast(S.Order_Ref_No AS Varchar(10))
    from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
     and P.DelID is null for xml path('')), 1, 2, '') + ']'
    
    select   @columns1 = (SELECT  distinct ',ISNULL([' + Cast(S.Order_Ref_No AS Varchar(10))+'],0)'  +  ' ['+Cast(S.Order_Ref_No AS Varchar(10))+']'
     from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
     and P.DelID is null   for xml path(''))
    
     set @columns1=SUBSTRING(@columns1,2,len(@columns1)-1)
    
    
     /* GRAND TOTAL COLUMN */
    DECLARE @GrandTotalCol	NVARCHAR (MAX)
    SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (S.Order_Ref_No AS Varchar(10)) +'],0) + ', 'ISNULL([' + CAST(S.Order_Ref_No AS Varchar(10))+ '],0) + ')
     from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate   and P.DelID is null 
    SET @GrandTotalCol = right(LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1),len(LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1))-17)
    
    set @convert =
    ('select codeitem, '+ @columns1+','+ @GrandTotalCol + ' as total  from (select S.Order_Ref_No,P.codeitem,P.prdqty from #Probale P inner join #salesorder S on S.OrderNo=P.orderno 
    	where  P.Entrydate between ''' + CONVERT(VARCHAR(20),@fromdate,121) + ''' and ''' + CONVERT(VARCHAR(20), @todate, 121) + '''
    	and P.DelID is null
    
     ) smallbale
        pivot(sum(prdqty) for Order_Ref_No
        in ('+@columns+')) as pivottable')
    
    execute (@convert)

    Best Regards 
    Echo   


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.




    • 已編輯 Echo Liuz 2020年8月14日 上午 06:06
    2020年8月14日 上午 05:56
  • Hi Echo Liuz,

    please check that row total still coming wrong,second thing i do not want to sum codeitem into row total

    below is data 

    CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
    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);
    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,'2020-01-13',null)
    , (2,3,1,001,200,'2020-01-15',null)
    , (3,11,1,002,200,'2020-01-15',null)
    , (5,10,1,002,200,'2020-01-16',null)
    , (6,1,1,003,200,'2020-01-16',null)
    , (7,3,1,003,200,'2020-01-17',null)
    , (8,1,1,003,200,'2020-01-13',null)
    , (9,3,1,003,200,'2020-01-13',null);
    
    INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
    , (002,'B_01','01-05-2019','Open')
    , (003,'C_01','01-05-2019','Open');
    
    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);
    
    
    
    declare @fromdate date='2020-01-13'
    declare @todate date='2020-01-15'
    declare @columns varchar(max)
    declare @convert varchar(max)
    declare @columns1 varchar(max)
    
    
    
    
    select   @columns = stuff (( select distinct'],[' +  Cast(S.Order_Ref_No AS Varchar(10))
    from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
     and P.DelID is null for xml path('')), 1, 2, '') + ']'
    
    select   @columns1 = (SELECT  distinct ',ISNULL([' + Cast(S.Order_Ref_No AS Varchar(10))+'],0)'  +  ' ['+Cast(S.Order_Ref_No AS Varchar(10))+']'
     from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
     and P.DelID is null   for xml path(''))
    
     set @columns1=SUBSTRING(@columns1,2,len(@columns1)-1)
    
    
     /* GRAND TOTAL COLUMN */
    DECLARE @GrandTotalCol	NVARCHAR (MAX)
    SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (S.Order_Ref_No AS Varchar(10)) +'],0) + ', 'ISNULL([' + CAST(S.Order_Ref_No AS Varchar(10))+ '],0) + ')
     from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate   and P.DelID is null 
    SET @GrandTotalCol = right(LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1),len(LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1))-17)
    
    set @convert =
    ('select Name, '+ @columns1+','+ @GrandTotalCol + '  from (select S.Order_Ref_No,P.codeitem,i.Descriptionitem as Name,P.prdqty from #Probale P 
    inner join #salesorder S on S.OrderNo=P.orderno inner join #ItemMasterFile i on i.codeitem=p.codeitem
    	where  P.Entrydate between ''' + CONVERT(VARCHAR(20),@fromdate,121) + ''' and ''' + CONVERT(VARCHAR(20), @todate, 121) + '''
    	and P.DelID is null
    
     ) smallbale
        pivot(sum(prdqty) for Order_Ref_No
        in ('+@columns+')) as pivottable')
    
    execute (@convert)
    drop table #SalesOrder
    drop table #OrderDetail
    drop table #Sections
    drop table #ItemMasterFile
    drop table #Probale

    Output



    akhter


    • 已編輯 Akhterhussain 2020年8月14日 上午 06:36 image upload
    2020年8月14日 上午 06:30
  • Hi Akhterhussain,

    I expanded the time range to show column C_01:

    declare @fromdate date='2019-01-13'
    declare @todate date='2021-01-15'
    declare @columns varchar(max)
    declare @convert varchar(max)
    declare @columns1 varchar(max)
    
    
    
    
    select   @columns = stuff (( select distinct'],[' +  Cast(S.Order_Ref_No AS Varchar(10))
    from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
     and P.DelID is null for xml path('')), 1, 2, '') + ']'
    
    select   @columns1 = (SELECT  distinct ',ISNULL([' + Cast(S.Order_Ref_No AS Varchar(10))+'],0)'  +  ' ['+Cast(S.Order_Ref_No AS Varchar(10))+']'
     from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
     and P.DelID is null   for xml path(''))
    
     set @columns1=SUBSTRING(@columns1,2,len(@columns1)-1)
    
    
     /* GRAND TOTAL COLUMN */
    DECLARE @GrandTotalCol	NVARCHAR (MAX)
    SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (S.Order_Ref_No AS Varchar(10)) +'],0) + ', 'ISNULL([' + CAST(S.Order_Ref_No AS Varchar(10))+ '],0) + ')
     from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate   and P.DelID is null 
    SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)
    
    set @convert =
    ('select Name, '+ @columns1+',('+ @GrandTotalCol + ')/2 as total  from (select S.Order_Ref_No,P.codeitem,i.Descriptionitem as Name,P.prdqty from #Probale P 
    inner join #salesorder S on S.OrderNo=P.orderno inner join #ItemMasterFile i on i.codeitem=p.codeitem
    	where  P.Entrydate between ''' + CONVERT(VARCHAR(20),@fromdate,121) + ''' and ''' + CONVERT(VARCHAR(20), @todate, 121) + '''
    	and P.DelID is null
    
     ) smallbale
        pivot(sum(prdqty) for Order_Ref_No
        in ('+@columns+')) as pivottable')
    
    execute (@convert)

    Best Regards 
    Echo


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    2020年8月14日 上午 08:31
  • Well, here is what you got for your GrandTotal and obviously it's wrong:

    ISNULL([A_01],0) + ISNULL ([A_01],0) + ISNULL ([B_01],0) 

    I can fix your code for you, but I was hoping you'll do an effort yourself. Anyway, here is how you can get your GrandTotal correctly - 

    if object_id('#TempResults', N'U') iS NOT NULL DROP TABLE #TempResults SELECT * -- list columns you need only here into #TempResults from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where P.Entrydate between @fromdate and @todate and P.DelID is null; select @columns = stuff (( select distinct ', ' + quotename(Cast(Order_Ref_No AS Varchar(10))) from #TempResults for xml path('')), 1, 2, '') select @columns1 = stuff((SELECT distinct ', ISNULL(' +

    quotename(CAST(Order_Ref_No AS Varchar(10))) +', 0) AS ' + quotename(Cast(Order_Ref_No AS Varchar(10))) from #TempResults for xml path('')), 1, 2,'') /* GRAND TOTAL COLUMN */ select @GrandTotals = stuff((SELECT distinct ' + ISNULL(' +

    quotename(CAST(Order_Ref_No AS Varchar(10))) + ', 0)' from #TempResults for xml path('')), 1, 3,'') select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total


    This is from the top of my head - you just need to first figure out what do you want to get as a static query and then may work backwards to get it. That's my usual approach for complex dynamic queries and it may take practice.


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • 已編輯 Naomi N 2020年8月16日 上午 03:06
    2020年8月14日 下午 02:38
  • Hi echo liuz ,

    my answer still not coming correct


    akhter

    2020年8月14日 下午 07:12
  • Did you see my response to you? Did you make an effort to use it?

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2020年8月14日 下午 07:18
  • hi Naomi N,

    I am working on your provided query,trying to solve it

    Msg 141, Level 15, State 1, Line 19
    A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
    Msg 102, Level 15, State 1, Line 19
    Incorrect syntax near ')'.
    Msg 156, Level 15, State 10, Line 26
    Incorrect syntax near the keyword 'AS'.


    akhter

    2020年8月14日 下午 07:31
  • I made a correction in my original reply, looks like STUFF function got missing from 2 last statements. Try again and if you can not make it post what you have so far, I'll try to correct it. 

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2020年8月14日 下午 07:39
  • Here is my current query,but how i will sum prdqty from Probale table

    if object_id('#TempResults', N'U') iS NOT NULL DROP TABLE #TempResults
    declare @fromdate date='2019-01-13'
    declare @todate date='2021-01-15'
    declare @columns varchar(max)
    declare @columns1 varchar(max)
    DECLARE @GrandTotals	NVARCHAR (MAX)
    
    SELECT * -- list columns you need only here
    into #TempResults
    from Probale P 
    inner join salesorder S on S.OrderNo=P.orderno 
    where P.Entrydate between @fromdate and  @todate
     and P.DelID is null;
    
    select @columns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(10)))
    from #TempResults
     for xml path('')), 1, 2, '') 
    
    select @columns1 = (SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(10))) +', 0) AS ' + quotename(Cast(Order_Ref_No AS Varchar(10)))
     from #TempResults 
      for xml path('')), 1, 2, '') 
    
    
     /* GRAND TOTAL COLUMN */
    
    select @GrandTotals = (SELECT  distinct ' + ISNULL(' + quotename(Order_Ref_No AS Varchar(10))) + ', 0)' 
     from #TempResults for xml path('')), 1, 3,'')
    
    select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total


    akhter

    2020年8月14日 下午 07:44
  • 1. Do you see my comment - List your columns here - this is what you actually need to do instead of *

    2. Try this change and let's do one step at a time:

    if object_id('#TempResults', N'U') iS NOT NULL DROP TABLE #TempResults declare @fromdate date='2019-01-13' declare @todate date='2021-01-15' declare @columns nvarchar(max) declare @columns1 nvarchar(max) DECLARE @GrandTotals NVARCHAR (MAX) ------- 1 ---- in the statement below use the actual column names instead of * SELECT * -- list columns you need only here into #TempResults from Probale P inner join salesorder S on S.OrderNo=P.orderno where P.Entrydate between @fromdate and @todate and P.DelID is null; ------ SELECT * from #TempResults --- examine what you got, if it's OK, comment out select @columns = stuff (( select distinct ', ' +

    quotename(Cast(Order_Ref_No AS Varchar(10))) from #TempResults for xml path('')), 1, 2, '') print @Columns -- this is to examine and verify it's OK, once you looked and verified, you can comment out select @columns1 = STUFF((SELECT distinct ', ISNULL(' +

    quotename(Cast(Order_Ref_No AS Varchar(10))) +', 0) AS ' + quotename(Cast(Order_Ref_No AS Varchar(10))) from #TempResults for xml path('')), 1, 2, '') print @columns1 --- Again, to verify, once satisfied, comment out /* GRAND TOTAL COLUMN */ select @GrandTotals = STUFF((SELECT distinct ' + ISNULL(' +

    quotename(CAST(Order_Ref_No AS Varchar(10))) + ', 0)' from #TempResults for xml path('')), 1, 3,'') select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total print @GrandTotals -- show here what you get before we proceed with next steps



    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • 已編輯 Naomi N 2020年8月14日 下午 08:35
    2020年8月14日 下午 07:50
  • getting Below error

    Msg 156, Level 15, State 10, Line 38
    Incorrect syntax near the keyword 'AS'.
     /* GRAND TOTAL COLUMN */
    
    select @GrandTotals = STUFF((SELECT  distinct ' + ISNULL(' + quotename(Order_Ref_No AS Varchar(10))) + ', 0)' 
     from #TempResults for xml path('')), 1, 3,'')


    akhter

    2020年8月14日 下午 08:00
  • Msg 2705, Level 16, State 3, Line 11
    Column names in each table must be unique. Column name 'OrderNo' in table '#TempResults' is specified more than once.

    if i execute below code..

    if object_id('#TempResults', N'U') iS NOT NULL DROP TABLE #TempResults
    declare @fromdate date='2019-01-13'
    declare @todate date='2021-01-15'
    declare @columns nvarchar(max)
    declare @columns1 nvarchar(max)
    DECLARE @GrandTotals	NVARCHAR (MAX)
    
    
    -------  1 ---- in the statement below use the actual column names instead of *
    
    SELECT * -- list columns you need only here
    into #TempResults
    from Probale P 
    inner join salesorder S on S.OrderNo=P.orderno 
    where P.Entrydate between @fromdate and  @todate
     and P.DelID is null;
    
    
    ------
    SELECT * from #TempResults --- examine what you got, if it's OK, comment out 
    
    select @columns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(10)))
    from #TempResults
     for xml path('')), 1, 2, '') 
    
    print @Columns -- this is to examine and verify it's OK, once you looked and verified, you can comment out
    


    akhter

    2020年8月14日 下午 08:20
  • CAST is missing here - 

    select @GrandTotals = STUFF((SELECT distinct

    ' + ISNULL(' +

    quotename(CAST(Order_Ref_No AS Varchar(10))) + ', 0)'

    from #TempResults for xml path('')), 1, 3,'') select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total



    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • 已編輯 Naomi N 2020年8月14日 下午 08:34
    2020年8月14日 下午 08:33
  • Hi Naomi,

    i tried to figure out solution ,but could not resolve

    Error

    (88489 row(s) affected)
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'TH-43'.

    Drop table #tempResults
    declare @fromdate date='2019-03-16'
    declare @todate date='2021-03-16'
    declare @columns nvarchar(max)
    declare @columns1 nvarchar(max)
    DECLARE @GrandTotals	NVARCHAR (MAX)
    
    
    -------  1 ---- in the statement below use the actual column names instead of *
    
    SELECT p.prdqty,s.order_ref_no 
    into #TempResults
    from Probale P 
    inner join salesorder S on S.OrderNo=P.orderno 
    where P.Entrydate between @fromdate and  @todate
     and P.DelID is null;
    
    
    ------
    --SELECT * from #TempResults --- examine what you got, if it's OK, comment out 
    
    select @columns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(50)))
    from #TempResults
     for xml path('')), 1, 2, '') 
    
    --print @Columns -- this is to examine and verify it's OK, once you looked and verified, you can comment out
    
    
    select @columns1 = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0) AS ' + quotename(Cast(Order_Ref_No AS Varchar(50)))
     from #TempResults 
      for xml path('')), 1, 2, '') 
    
    --print @columns1 --- Again, to verify, once satisfied, comment out
    
    
     select @GrandTotals = STUFF((SELECT  distinct ' , ISNULL(' + quotename(CAST(Order_Ref_No AS Varchar(50))) + ', 0) '
    
     from #TempResults for xml path('')), 1, 3,'')
    
    select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total
    
    select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total
    
    execute (@GrandTotals) -- show here what you get before we proceed with next steps


    akhter

    2020年8月15日 上午 07:15
  • i tried to figure out solution ,but could not resolve

    Error

    (88489 row(s) affected)
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'TH-43'.

    Add this line:

    PRINT @GrandTotals

    Looking at the output, you may even be able to understand the error yourself.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    2020年8月15日 上午 08:16
  • i add below

    select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total
    PRINT (@GrandTotals)
    execute (@GrandTotals)

    but issue is same 

    (88489 row(s) affected)
    ISNULL([TH-43], 0)  , ISNULL([RK-05], 0)  , ISNULL([FZ-12], 0)  , ISNULL([TH-47], 0)  , ISNULL([SH-30], 0)  , ISNULL([SG-32], 0)  , ISNULL([ML-15], 0)  , ISNULL([KS-04], 0)  , ISNULL([HP-18], 0)  , ISNULL([SH-33], 0)  , ISNULL([JM-47], 0)  , ISNULL([NK-05], 0)  , ISNULL([AC-10], 0)  , ISNULL([AZ-74], 0)  , ISNULL([AZ-78], 0)  , ISNULL([ML-EX], 0)  , ISNULL([AP-02], 0)  , ISNULL([AZ-79], 0)  , ISNULL([SH-37], 0)  , ISNULL([PH-17], 0)  , ISNULL([FZ-07], 0)  , ISNULL([TH-36], 0)  , ISNULL([MT-01], 0)  , ISNULL([AG-05], 0)  , ISNULL([DK-10], 0)  , ISNULL([AZ-76], 0)  , ISNULL([BNV], 0)  , ISNULL([KS-SP], 0)  , ISNULL([DK-12], 0)  , ISNULL([FZ-19], 0)  , ISNULL([AT-03], 0)  , ISNULL([JM-45], 0)  , ISNULL([AZ-71], 0)  , ISNULL([JM-39], 0)  , ISNULL([MT-03], 0)  , ISNULL([PH-18], 0)  , ISNULL([RK-07], 0)  , ISNULL([BL-14], 0)  , ISNULL([JM-EX], 0)  , ISNULL([FG], 0)  , ISNULL([HP-19], 0)  , ISNULL([TH-44], 0)  , ISNULL([HP-21], 0)  , ISNULL([BL-EX], 0)  , ISNULL([AZ-80], 0)  , ISNULL([FG-10], 0)  , ISNULL([FZ-06], 0)  , ISNULL([ML-16], 0)  , ISNULL([FG-08], 0)  , ISNULL([TH-45], 0)  , ISNULL([FZ-05], 0)  , ISNULL([HP-20], 0)  , ISNULL([AG-02], 0)  , ISNULL([RK-04], 0)  , ISNULL([NK-03], 0)  , ISNULL([FG-09], 0)  , ISNULL([TH-37], 0)  , ISNULL([B-32], 0)  , ISNULL([TH-42], 0)  , ISNULL([AZ-73], 0)  , ISNULL([JM-38], 0)  , ISNULL([JM-40], 0)  , ISNULL([DK-09], 0)  , ISNULL([JM-42], 0)  , ISNULL([AG-04], 0)  , ISNULL([RK-SP], 0)  , ISNULL([FZ-08], 0)  , ISNULL([KS-03], 0)  , ISNULL([SH-31], 0)  , ISNULL([KS-07], 0)  , ISNULL([AZ-72], 0)  , ISNULL([FZ-13], 0)  , ISNULL([JM-44], 0)  , ISNULL([FZ-17], 0)  , ISNULL([B-30], 0)  , ISNULL([AZ-70], 0)  , ISNULL([ML-17], 0)  , ISNULL([KS-05], 0)  , ISNULL([FZ-14], 0)  , ISNULL([PH-23], 0)  , ISNULL([FZ-10], 0)  , ISNULL([BL-15], 0)  , ISNULL([MX-P], 0)  , ISNULL([ZML], 0)  , ISNULL([AZ-77], 0)  , ISNULL([AZ-75], 0)  , ISNULL([TH-41], 0)  , ISNULL([HP-23], 0)  , ISNULL([SH-36], 0)  , ISNULL([PH-19], 0)  , ISNULL([TZ-01], 0)  , ISNULL([FZ-16], 0)  , ISNULL([DK-13], 0)  , ISNULL([KS-06], 0)  , ISNULL([AP-03], 0)  , ISNULL([KS-02], 0)  , ISNULL([ML-19], 0)  , ISNULL([SH-38], 0)  , ISNULL([JM-43], 0)  , ISNULL([PH-21], 0)  , ISNULL([SH-34], 0)  , ISNULL([AZ-81], 0)  , ISNULL([MT-04], 0)  , ISNULL([CH-02], 0)  , ISNULL([KS-01], 0)  , ISNULL([PK], 0)  , ISNULL([F-J], 0)  , ISNULL([NK-04], 0)  , ISNULL([SH-35], 0)  , ISNULL([FZ-11], 0)  , ISNULL([HP-24], 0)  , ISNULL([FZ-18], 0)  , ISNULL([NK-06], 0)  , ISNULL([TH-46], 0)  , ISNULL([JM-41], 0)  , ISNULL([NK-07], 0)  , ISNULL([AZ-69], 0)  , ISNULL([MT-02], 0)  , ISNULL([SG-33], 0)  , ISNULL([NK-02], 0)  , ISNULL([JM-SP], 0)  , ISNULL([NK-01], 0)  , ISNULL([AT-04], 0)  , ISNULL([B-31], 0)  , ISNULL([TH-39], 0)  , ISNULL([SH-32], 0)  , ISNULL([SD], 0)  , ISNULL([AC-11], 0)  , ISNULL([RK-06], 0)  , ISNULL([PH-20], 0)  , ISNULL([AG-01], 0)  , ISNULL([DK-14], 0)  , ISNULL([ML-18], 0)  , ISNULL([DK-08], 0)  , ISNULL([BS-EX], 0)  , ISNULL([HP-17], 0)  , ISNULL([DK-11], 0)  , ISNULL([TH-35], 0)  , ISNULL([TZ-02], 0)  , ISNULL([TH-40], 0)  , ISNULL([AG-03], 0)  , ISNULL([PH-22], 0)  , ISNULL([TH-48], 0)  , ISNULL([TH-38], 0)  , ISNULL([FZ-15], 0)  , ISNULL([HP-22], 0)  , ISNULL([CH-EX], 0)  , ISNULL([BL-13], 0)  , ISNULL([JM-46], 0)  , ISNULL([SP], 0)  , ISNULL([FZ-09], 0)  AS [Total]  AS [Total] 
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'TH-43'.
    


    akhter

    2020年8月15日 上午 08:49
  • select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total
    PRINT (@GrandTotals)
    execute (@GrandTotals)

    but issue is same 

    Of course. The purpose of the PRINT is to see what you have produced. And you have produced nonsense. I mean, does that look like valid SQL to you?

    You need to go back and try to understand what you are trying to do.

    However, I'm kinda curious. Where do you aim do display all this pivot data? You don't have to do the pivoting in SQL Server. In fact, pivoting is a presentation device, so it is often best done in the presentation layer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    2020年8月15日 下午 09:23
  • @GrandTotals is part of the select, you cannot execute it. Why did you even try if I asked you to print it to verify first what you got?

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2020年8月16日 上午 02:43
  • I'm sorry, but did you even try to read what I wrote and see the comments I put for you? What do you think these comments meant?

    -------  1 ---- in the statement below use the actual column names instead of *
    
    SELECT * -- list columns you need only here

    They are instructions what YOU SHOULD DO by yourself.


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2020年8月16日 上午 02:46
  • BTW, why did you change what we had in @GrandTotals? Do you see that this is not what we wanted to get after you changed '+' into ',' ? Do you understand the idea behind the @Columns1 and @GrandTotals?

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2020年8月16日 上午 02:50
  • hi Naomi N,

    i tried alot ,but not getting done,your humbly requested to do favour to create whole query ,to sum rows and columns wise total please.


    akhter

    2020年8月18日 上午 02:00
  • Ok, I'll do that later tonight since I'm going for a walk now.

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2020年8月18日 上午 02:15
  • Thanks Alot,

    My requirement is,

    Row and Column wise Total,with date filter


    akhter

    2020年8月18日 上午 02:46
  • hi Naomi N,

    i tried alot ,but not getting done,your humbly requested to do favour to create whole query ,to sum rows and columns wise total please.


    I repeat my question: where do  you intend to consume this result? There are very good chances that you are better off performing the pivoting elsewhere and return a regular result set from SQL Server.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    2020年8月18日 上午 10:44
  • HI Erland Sommkarskog,

    where do  you intend to consume this result?   i do not understand you.


    akhter

    2020年8月18日 上午 10:53
  • Erland is asking - once you got the results, how are you going to use them? Are you writing a stored procedure or direct query - which application is going to use the results and how?

    And sorry for not replying last night - after the walk I went right to bed (it was after 10pm). So I'm going to start my day from trying to help you now.


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2020年8月18日 下午 01:32
  • Ok, here is your whole query - I changed names of the variables to give them better meaning:

    CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
    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);
    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,'2020-01-13',null)
    , (2,3,1,001,200,'2020-01-15',null)
    , (3,11,1,002,200,'2020-01-15',null)
    , (5,10,1,002,200,'2020-01-16',null)
    , (6,1,1,003,200,'2020-01-16',null)
    , (7,3,1,003,200,'2020-01-17',null);
    
    INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
    , (002,'B_01','01-05-2019','Open')
    , (003,'C_01','01-05-2019','Open');
    
    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);
    
    
    
    ---------------------
    declare @fromDate date='2020-01-13';
    declare @toDate date='2020-01-15';
    
    declare @pvtColumns nvarchar(max), @sqlQuery nvarchar(max), @qryColumns nvarchar(max), @totalsExpression nvarchar(max);
    
    drop table if exists #TempResults;
    
    SELECT p.prdqty,s.order_ref_no, p.CodeItem 
    into #TempResults
    from #Probale P 
    inner join #salesorder S on S.OrderNo=P.orderno 
    where P.Entrydate between @fromdate and  @todate
     and P.DelID is null;
    
    select @pvtColumns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(50)))
    from #TempResults
     for xml path('')), 1, 2, ''); 
    
    select @qryColumns = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0) AS ' + 
    quotename(Cast(Order_Ref_No AS Varchar(50)))
     from #TempResults 
      for xml path('')), 1, 2, ''); 
    
    select @totalsExpression = STUFF((SELECT  distinct ' + ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0)' 
     from #TempResults 
      for xml path('')), 1, 3, '');
    
    set @sqlQuery =
    'select codeItem, '+ @qryColumns +' , '+ @totalsExpression + ' as [Row Total] from #TempResults
    pivot(sum(prdqty) for Order_Ref_No
        in (' + @pvtColumns + ')) as pivottable';
    
    print @sqlQuery;
    
    execute (@sqlQuery)
    
    drop table #SalesOrder
    drop table #OrderDetail
    drop table #Sections
    drop table #ItemMasterFile
    drop table #Probale
    drop table if exists #TempResults;


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2020年8月18日 下午 01:44
  • Hi Naomi,

    last requirement for column wise total .please 


    akhter

    2020年8月18日 下午 07:52
  • So, do you want to add one extra final row for the totals column based (because I already have one row based)? Can you show the desired output?

    The reason I am asking is that it's not very easy using PIVOT syntax, I think. Much easier using case based pivot (for the actual PIVOT syntax I need to figure out the best solution - especially for dynamic PIVOT we're doing). Need a bit of research. 


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • 已編輯 Naomi N 2020年8月18日 下午 08:41
    2020年8月18日 下午 08:36
  • where do  you intend to consume this result?   i do not understand you.

    You are not running queries out of thin air, but presumably this is do be displayed in an application of some sort. That would be a web application, a report in Reporting Services, an Excel file, Access, or even be exported to the file.

    So what will happnen with the result set when it leaves SQL Server?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    2020年8月18日 下午 09:17
  • Try

    CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
    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);
    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,'2020-01-13',null)
    , (2,3,1,001,200,'2020-01-15',null)
    , (3,11,1,002,200,'2020-01-15',null)
    , (5,10,1,002,200,'2020-01-16',null)
    , (6,1,1,003,200,'2020-01-16',null)
    , (7,3,1,003,200,'2020-01-17',null);
    
    INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
    , (002,'B_01','01-05-2019','Open')
    , (003,'C_01','01-05-2019','Open');
    
    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);
    
    
    
    ---------------------
    declare @fromDate date='2020-01-13';
    declare @toDate date='2020-01-15';
    
    declare @pvtColumns nvarchar(max), @sqlQuery nvarchar(max), @qryColumns nvarchar(max), @totalsExpression nvarchar(max);
    
    drop table if exists #TempResults;
    
    SELECT p.prdqty,s.order_ref_no, p.CodeItem 
    into #TempResults
    from #Probale P 
    inner join #salesorder S on S.OrderNo=P.orderno 
    where P.Entrydate between @fromdate and  @todate
     and P.DelID is null;
    
    select @pvtColumns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(50)))
    from #TempResults
     for xml path('')), 1, 2, ''); 
    
    select @qryColumns = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0) AS ' + 
    quotename(Cast(Order_Ref_No AS Varchar(50)))
     from #TempResults 
      for xml path('')), 1, 2, ''); 
    
    select @totalsExpression = STUFF((SELECT  distinct ' + ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0)' 
     from #TempResults 
      for xml path('')), 1, 3, '');
    
    
    select CodeItem, sum(prdQty) as Qty, Order_Ref_No from #TempResults
    GROUP BY order_Ref_No, ROLLUP(CodeItem)
    
    set @sqlQuery =
    'select ISNULL(CAST(codeItem as varchar(100)), ''Total:'')  as Item, '+ @qryColumns +' , '+ @totalsExpression + ' as [Row Total] from 
    
    (select CodeItem, sum(prdQty) as Qty, Order_Ref_No from #TempResults
    GROUP BY order_Ref_No, ROLLUP(CodeItem)) sm
    pivot(sum(Qty) for Order_Ref_No
        in (' + @pvtColumns + ')) as pivottable
    order by ISNULL(CodeItem, 99999999)';
    
    --print @sqlQuery;
    
    execute (@sqlQuery)
    
    drop table #SalesOrder
    drop table #OrderDetail
    drop table #Sections
    drop table #ItemMasterFile
    drop table #Probale
    drop table if exists #TempResults;


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • 已標示為解答 Akhterhussain 2020年8月19日 上午 03:08
    • 已取消標示為解答 Akhterhussain 2020年8月19日 上午 03:14
    2020年8月18日 下午 10:00
  • hi Naomi,

    i little modified my query which is below,i used descriptionitem instead of codeitem,then row total coming in middle

    declare @fromDate date='2020-01-13';
    declare @toDate date='2020-01-15';
    
    declare @pvtColumns nvarchar(max), @sqlQuery nvarchar(max), @qryColumns nvarchar(max), @totalsExpression nvarchar(max);
    
    
    
    SELECT p.prdqty,s.order_ref_no, i.Descriptionitem 
    into #TempResults
    from Probale P 
    inner join salesorder S on S.OrderNo=P.orderno 
    inner join itemmasterfile i on i.codeitem=p.codeitem
    where P.Entrydate between @fromdate and  @todate
     and P.DelID is null;
    
    select @pvtColumns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(50)))
    from #TempResults
     for xml path('')), 1, 2, ''); 
    
    select @qryColumns = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0) AS ' + 
    quotename(Cast(Order_Ref_No AS Varchar(50)))
     from #TempResults 
      for xml path('')), 1, 2, ''); 
    
    select @totalsExpression = STUFF((SELECT  distinct ' + ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0)' 
     from #TempResults 
      for xml path('')), 1, 3, '');
    
    
    --select CodeItem, sum(prdQty) as Qty, Order_Ref_No from #TempResults
    --GROUP BY order_Ref_No, ROLLUP(CodeItem)
    
    set @sqlQuery =
    'select ISNULL(CAST(Descriptionitem as varchar(100)), ''Total:'')  as Descriptionitem, '+ @qryColumns +' , '+ @totalsExpression + ' as [Row Total] from 
    
    (select Descriptionitem, sum(prdQty) as Qty, Order_Ref_No from #TempResults
    GROUP BY order_Ref_No, ROLLUP(Descriptionitem)) sm
    pivot(sum(Qty) for Order_Ref_No
        in (' + @pvtColumns + ')) as pivottable
    order by ISNULL(Descriptionitem, 99999999)';
    
    --print @sqlQuery;
    
    execute (@sqlQuery)
    drop table #TempResults;

    ouput


    akhter

    2020年8月19日 上午 03:17
  • Change ORDER BY this way:

    case when pivotTable.DescriptionItem IS NULL then 1 else 0 end, DescriptionItem


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2020年8月19日 上午 05:28
  • Also, it's is not a nice idea to change requirements or code and not even try to make the correct changes yourself:

    CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
    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);
    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,'2020-01-13',null)
    , (2,3,1,001,200,'2020-01-15',null)
    , (3,11,1,002,200,'2020-01-15',null)
    , (5,10,1,002,200,'2020-01-16',null)
    , (6,1,1,003,200,'2020-01-16',null)
    , (7,3,1,003,200,'2020-01-17',null);
    
    INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
    , (002,'B_01','01-05-2019','Open')
    , (003,'C_01','01-05-2019','Open');
    
    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);
    
    
    
    ---------------------
    declare @fromDate date='2020-01-13';
    declare @toDate date='2020-01-15';
    
    declare @pvtColumns nvarchar(max), @sqlQuery nvarchar(max), @qryColumns nvarchar(max), @totalsExpression nvarchar(max);
    
    drop table if exists #TempResults;
    
    SELECT p.prdqty,s.order_ref_no, i.Descriptionitem 
    into #TempResults
    from #Probale P 
    inner join #salesorder S on S.OrderNo=P.orderno 
    inner join #itemmasterfile i on i.codeitem=p.codeitem
    where P.Entrydate between @fromdate and  @todate
     and P.DelID is null;
    
    select @pvtColumns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(50)))
    from #TempResults
     for xml path('')), 1, 2, ''); 
    
    select @qryColumns = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0) AS ' + 
    quotename(Cast(Order_Ref_No AS Varchar(50)))
     from #TempResults 
      for xml path('')), 1, 2, ''); 
    
    select @totalsExpression = STUFF((SELECT  distinct ' + ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0)' 
     from #TempResults 
      for xml path('')), 1, 3, '');
    
    
    
    
    set @sqlQuery =
    'select ISNULL(DescriptionItem, ''Total:'')  as DescriptionItem, '+ @qryColumns +' , '+ @totalsExpression + ' as [Row Total] from 
    
    (select DescriptionItem, sum(prdQty) as Qty, Order_Ref_No from #TempResults
    GROUP BY order_Ref_No, ROLLUP(DescriptionItem)) sm
    pivot(sum(Qty) for Order_Ref_No
        in (' + @pvtColumns + ')) as pivottable
    order by case when PivotTable.DescriptionItem IS NULL then 1 else 0 end, DescriptionItem';
    
    --print @sqlQuery;
    
    execute (@sqlQuery)
    
    drop table #SalesOrder
    drop table #OrderDetail
    drop table #Sections
    drop table #ItemMasterFile
    drop table #Probale
    drop table if exists #TempResults;


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2020年8月19日 上午 05:33
  • Thanks Alot Naomi,

    for bearing me......

    Thanks Alot again,,,,,next query is soon post


    akhter

    2020年8月19日 上午 06:47