Ramco Ramco

Ramco Ramco

  • 803
  • 2.5k
  • 273.2k

Totals in Pivot

May 8 2022 11:16 AM


 I have below code & i want to display Totals . Is it possible to save pivot data in another temp table.

CREATE TABLE #temp(Code nvarchar(10),Name
Quantity numeric(19,6),itemname nvarchar(50))

insert into #temp
select T0.Code 'Code',T0.Name 'Name',
(sum(T2.Quantity)) 'Quantity',Max(T3.ItemName) 'Item Description'
From Customer T0
Left Outer Join PO0 T1 on T0.CardCode = T1.CardCode
inner join PO1 T2 on T1.DocEntry = T2.docentry
inner join Items T3 on T2.ItemCode = T3.ItemCode
where T1.DATE >= '2022/03/16' and T1.DOCDATE <= '2022/04/16' 
GROUP BY T0.Code,T0.Name,T2.ItemCode
declare @cols as nvarchar(max)='';
declare @query as nvarchar(max)='';
select @cols = @cols + QUOTENAME(ItemName) + ',' from (Select distinct ItemName from #temp) as tmp
select @cols = substring(@cols,0,len(@cols))
set @query = 'select Name, ' + @cols + ' from (select name,quantity,itemname from #temp) x
pivot (Sum(quantity) for itemname in (' + @cols + ')) piv ';
execute (@query)


Answers (5)