CODEWITHSUNDEEP
sqlsql-serverdynamic
Otshepeng Ditshego
Otshepeng Ditshego

Reputation: 197

Save dynamic table as a view/table

I just wrote a script here but I have no idea how to save it as a view or a table. It is dynamically creating columns from rows 

 DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT  @columns += N', p.' + QUOTENAME([Period])
  FROM (SELECT p.Period FROM dbo.[refv_IRR3_Op_Rev] AS p
  INNER JOIN [dbo].[refv_IRR3_Op_Rev] AS o
  ON p.RMDF = o.RMDF 
  GROUP BY P.Period) AS x;
SET @sql = N'
SELECT [Region]
      ,[LAU]
      ,[RMDF]
      , ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT [Region]
      ,[LAU]
      ,[RMDF]
      ,[Period]
      ,[Net_Operating_Cashflow]
  FROM [FTTx_Build].[dbo].[refv_IRR3_Op_Rev]

) AS j
PIVOT
(
  SUM([Net_Operating_Cashflow]) FOR Period IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

How do I save it as a view or table?

Upvotes: 0

Views: 50

Answers (1)

user1443098
user1443098

Reputation: 7645

short answer: you can't. However you can build a stored procedure from your code and call that when you need those results.

Upvotes: 1

Related Questions