Dynamically generating Table in Sql Server from columns name returned from a query

See more:

SQL

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 

-- exec [getPivotData] '[TPDOC VOLUME]','201048','Australia'