sql-server - 必须声明表变量 "@myTable"

Question

DECLARE @myTable TABLE( 
Name VARCHAR(50), 
Year INT, 
Hours INT ) 

INSERT INTO @myTable *.... some values*

DECLARE @var INT 
SET @var = 2015

DECLARE @DynamicPivot VARCHAR(MAX) 

SET @DynamicPivot = 'SELECT * FROM
@myTable  PIVOT( SUM(Hours) FOR Year IN (' + @var + ') ) AS PvtTable

EXEC sp_executesql @DynamicPivot

我正在尝试创建一个动态表并使用枢轴旋转该表。 @var 中的值是动态表 Year INT 中已存在的值。一切工作正常，除了当我尝试执行动态枢轴时。它给了我一个错误，即使我同时运行整个代码，也没有声明 @myTable 。问题可能出在枢轴声明中，我并没有真正找到问题。有什么想法吗？

Answer 1

使用#临时表，sp_executesql仅适用于nvarchar:

CREATE TABLE #myTable ( 
    Name VARCHAR(50), 
    Year INT, 
    Hours INT
) 

INSERT INTO #myTable *.... some values*

DECLARE @var INT 
SET @var = 2015

DECLARE @DynamicPivot NVARCHAR(MAX) 

SET @DynamicPivot = '
SELECT * 
FROM #myTable
PIVOT( 
SUM(Hours) FOR Year IN ([' + CAST(@var as nvarchar(10)) + '])
) AS PvtTable'

EXEC sp_executesql @DynamicPivot

DROP TABLE #myTable