Hello All,
I am generating a pivot table,I also want to show the Grand Total field at the end
So I want to generate a table dynamically but I will not know in advance as to how
many columns it would have.Only I know in advance is the datatype is numeric(18,2).
Now can anyone help me to resolve this .
ALTER proc [dbo].[GetPivotData] @measure nvarchar(200),@Week NVARCHAR(100),@country nvarchar(100)
as
begin
DECLARE @SQL NVARCHAR(max)
DECLARE @OpID nvarchar(100)
DECLARE @cols NVARCHAR(2000)
DECLARE @QUERY NVARCHAR(4000)
Create Table #TempTbl(Week BIGINT,Measure numeric(18,2),Operator nvarchar(100))
DECLARE @getOperators CURSOR
SET @getOperators = CURSOR FOR Select distinct(Operator)from Data
OPEN @getOperators
FETCH NEXT
FROM @getOperators INTO @OpID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL='SELECT TOP (8) ISNULL(Week,0) AS Week,'+@measure+' ,Operator='''+@OpID+''' FROM Data WHERE (Country ='''+@country+''') AND (Operator = '''+ @OPID +''')
AND ( Week <'''+@Week+''') ORDER BY Week DESC'
Insert #TempTbl EXEC(@SQL)
FETCH NEXT
FROM @getOperators INTO @OpID
END
CLOSE @getOperators
DEALLOCATE @getOperators
SELECT @cols = STUFF(( SELECT distinct '],['+Operator FROM #TempTbl order by '],['+Operator FOR XML PATH('') ),1,2,'') + ']'
SET @QUERY='Select Week,'+@cols+' from(Select * from #TempTbl) as mytbl
pivot(sum(Measure)for Operator in ('+@cols+'))as PVTTBL Order by Week DESC'
EXEC(@QUERY)
END