title: Row Total In Dynamic Pivot
body: <p>I'm creating A Report that has Row Total in Dynamic Pivot.
  How to work with it?</p>
  
  <p>Here's my code below:</p>
  
  <p>DECLARE @Query VARCHAR(8000)</p>
  
  <pre><code>DECLARE @Col VARCHAR(8000)
  DECLARE @whse VARCHAR(20)
  Declare @dDate VARCHAR(20)
  DECLARE @itemcode VARCHAR(8000)
  Declare @shift varchar(20)
  Declare @DocEntry VARCHAR(MAX)
  DECLARE @isnullCol VARCHAR(8000)
  
  Set @dDate =/* SELECT convert(varchar(15),Max(T0.DocDate),112) 
  FROM OINM T0 WHERE T0.DocDate = */ convert(varchar(15),'20190602',112)
  
  Set @whse = (Select max(a1.WhsCode) 
  From OWHS a1 Where a1.WhsCode='A3 P-FG')
  
  Set @shift = (select max(a2.u_shift)  
  from OQUT a2 where a2.u_shift='2nd Shift')
  
  SET @itemcode =  isnull( STUFF((Select DISTINCT ',  ''' + T1.ITEMCODE+'''' 
  from OWTQ T0 
  INNER JOIN WTQ1 t1 ON T0.DOCENTRY=T1.DOCENTRY
  where t0.Docdate=@dDate AND T1.[FromWhsCod]=@whse 
  and t0.u_Shift=@shift FOR XML PATH('')),1,1,'') ,0)
  
  SET @DocEntry =  isnull( STUFF((Select distinct  ',' +     convert(varchar(10),T0.DocEntry) 
  from OWTQ T0 INNER JOIN WTQ1 t1 ON T0.DOCENTRY=T1.DOCENTRY
  where t0.Docdate=@dDate AND T1.[FromWhsCod]=@whse 
  and t0.u_Shift=@shift FOR XML PATH('')),1,1,'') ,0)
  
  
  
  SELECT  @Col = STUFF(( SELECT DISTINCT '],[' + ltrim((t1.whscode)) 
  from OWTQ T0  INNER JOIN WTQ1 T1 ON T0.DocEntry = T1.DocEntry
  where T0.Docdate=@dDate and T1.[FromWhsCod]=@whse 
  and t0.u_Shift like '%'+@shift+'%' 
  ORDER BY '],[' + ltrim((t1.whscode)) 
  FOR XML PATH('') ), 1, 2, '') + ']'
  
  SELECT @col += ',[RTotal]'
  SELECT @isnullCol = 
  SUBSTRING((SELECT DISTINCT ',ISNULL(['+t1.whscode+'],0) AS ['+whscode+']' 
  from OWTQ T0  INNER JOIN WTQ1 T1 ON T0.DocEntry = T1.DocEntry
  where T0.Docdate=@dDate and T1.[FromWhsCod]=@whse 
  and t0.u_Shift like '%'+@shift+'%' 
  FOR XML PATH('')),2,8000)
  
  SELECT @isnullCol += ',ISNULL([RTotal],0) AS [Total]'
  
  
  
  Set @Query = 
  'Select  pvt.itemcode,'+@isnullCol+' from 
  (Select ISNULL(CAST(Itemcode AS         
  VARCHAR(50)),''RTotal'')Itemcode,t1.whscode,sum(t1.Quantity) as Total
  FROM OWTQ T0 INNER JOIN WTQ1 T1 ON T0.DocEntry=T1.DocEntry
  Where convert(varchar(15),T0.Docdate,112) = 
  convert(varchar(15),'+@dDate+',112)  and t1.itemcode IN ('+ @itemcode +') 
  and t0.DocEntry IN (' + @DocEntry + ')
  group by t1.itemcode,t1.whscode )src
  PIVOT (SUM(Total) For WhsCode IN ('+@Col+')) as pvt'
  
  EXECUTE (@Query)
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/BEtoC.png" rel="nofollow noreferrer">enter image description here</a></p>