sql - 有没有办法从动态 SQL 查询创建视图或临时表

Question

我有一个查询，它提供了一些我想将这些数据保存在临时表或视图中的数据。请帮忙

查询如下：

IF OBJECT_ID('tempdb..#NLSPMTS3_tmp') IS NOT NULL 
    DROP TABLE #NLSPMTS3_tmp

SELECT * 
INTO #NLSPMTS3_tmp 
FROM vwNLSPayments4

DECLARE @Columns AS VARCHAR(MAX)

SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME(b.mop_fpd)
FROM (SELECT DISTINCT mop_fpd FROM #NLSPMTS3_tmp) AS B
ORDER BY B.mop_fpd
 
-- You can't remove nulls from a PIVOT, so you have to create a second set of column names
DECLARE @NullColumns as VARCHAR(MAX)
SELECT @NullColumns =
       COALESCE(@NullColumns + ', ','') + 'ISNULL(' + QUOTENAME(b.mop_fpd) + ', 0) ' + QUOTENAME(b.mop_fpd)
       FROM (Select DISTINCT mop_fpd FROM #NLSPMTS3_tmp) AS B
       ORDER BY B.mop_fpd
 
DECLARE @SQL as NVARCHAR(MAX)
SET @SQL = 'SELECT acctrefno, ' + @NullColumns + '
                     FROM
                           (SELECT payment_amount, acctrefno, mop_fpd
                     FROM
                           nlsdev.dbo.vwnlsPayments2
                     ) as PivotData
                     PIVOT
                     (sum(payment_amount) FOR mop_fpd IN (' + @Columns + ')) AS PivotResult ORDER BY acctrefno'
EXEC (@SQL)

我只是简单地将创建视图设置为，<above SQL query>但它不起作用。

标签： sqlsql-server