I am using SQL Server 2008.
I have a T-SQL code in which I am using WITH ROLLUP clause to get Grand Total as footer row and last column. With the below code I am getting last row as Grand Total but I am not getting last column as Grand Total.
Is it possible to use WITH ROLLUP to get Grand Total column as well?
Below is the extract of my T-SQL code. The second last line is where I am using the WITH ROLLUP clause.
declare @sql as varchar(max)
set @sql = 'insert into #TempReport select [Country],' + @cols + '
C.Country_Name AS [Country],
D.GSL_Name AS [GSL],
SUM(A.Allocation) AS Total
from Work_Information A
inner join Sales B
on A.Sales_ID = B.Sales_Id
inner join Countries C
on A.Country_ID = C.Country_ID
inner join tbl_GSL D
on A.GSL_ID = D.GSL_ID AND A.Sales_ID = D.Sales_Id
where A.Sales_Id = 1
group by A.Country_ID,C.Country_Name, A.Sales_ID, A.GSL_ID,D.GSL_Name
) P Pivot (sum(Total) for [GSL] in (' + @Cols + ')) as Pvt'
declare @SumCols as varchar(max)
select @SumCols = stuff(
(select ', Sum(' + quotename(GSLName) + ')' + REPLACE(GSLName,' ','') from
select TOP 200 GSL_Name as GSLName
from tbl_GSL GSL
where Sales_Id = 1
ORDER BY GSL_Name
ORDER BY GSLName FOR XML PATH('')
declare @sql3 as varchar(1000)
set @sql3 = 'select Country,' + @SumCols + ' from #TempReport group by Country WITH ROLLUP'