Totals in Pivot table

Ramco Ramco

I have below data . I want to display Totals. Items are dynamic. It can be Item1 , Item2 , Item3 , Item4 & so on.

Temp Table

CREATE TABLE #temp(Location nvarchar(max),
Quantity numeric(19,6),itemName nvarchar(50))

Thanks

5 Replies

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

Amit Mohanty4y ago
You don't have to define anything, just run the query as it is.
Ramco Ramco4y ago
Hi Amit

If there are 10 cols then do i need to define col1,col2 till 10

Thanks
Amit Mohanty4y ago
Hey Ramco, no problem how many items are there, it will work.
Ramco Ramco4y ago
Hi Amit

Columns are dynamic.It can be 2 ,5, 10 & so on.

Thanks
Amit Mohanty4y ago
Hey, please try the below query.
declare @cols as nvarchar(max), @cols1 as nvarchar(max), @cols2 as nvarchar(max);
declare @query as nvarchar(max)='';
select @cols = coalesce(@cols + ', ', '') + 'Sum(IsNull([' + ItemName + '],0)) ' + ItemName from (Select distinct ItemName from #temp) as tmp
select @cols1 = coalesce(@cols1 + ' + ', '') + 'Sum(IsNull([' + ItemName + '],0))' from (Select distinct ItemName from #temp) as tmp
select @cols2 = coalesce(@cols2 + ', ', '') + ItemName from (Select distinct ItemName from #temp) as tmp
set @query = 'select Location, ' + @cols + ', ' + @cols1 + ' as Total from (select Location,Quantity,itemname from #temp) x pivot
( Sum(quantity) for itemname in (' + @cols2 + ')) piv  group by Location';
execute (@query)