SQL Server中表值转置方法及表变量替换报错问题求助

一、解决表变量报错“Must declare the scalar variable "@table"” #

这个报错大多是因为你在动态SQL里引用了外部声明的表变量——动态SQL有独立的执行上下文，外部表变量无法直接被内部识别，给你两个可行的解决办法：

• 改用临时表替代表变量：临时表的作用域覆盖动态SQL，不会有识别问题

-- 创建临时表替代表变量
CREATE TABLE #temp (StudentID INT, Subject NVARCHAR(50), Score INT)
INSERT INTO #temp VALUES (1, 'Math', 90), (1, 'English', 85), (2, 'Math', 88)

-- 动态SQL里直接引用#temp
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM #temp PIVOT (MAX(Score) FOR Subject IN ([Math], [English])) AS pvt'
EXEC sp_executesql @sql

-- 用完删除临时表
DROP TABLE #temp

• 必须用表变量的话，通过sp_executesql传递参数：把表变量作为参数传入动态SQL内部

DECLARE @table TABLE (StudentID INT, Subject NVARCHAR(50), Score INT)
INSERT INTO @table VALUES (1, 'Math', 90), (1, 'English', 85), (2, 'Math', 88)

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM @tbl PIVOT (MAX(Score) FOR Subject IN ([Math], [English])) AS pvt'
-- 定义参数并传递表变量
EXEC sp_executesql @sql, N'@tbl TABLE (StudentID INT, Subject NVARCHAR(50), Score INT)', @tbl = @table

二、用PIVOT处理Score列完成转置 #

假设原表/表变量结构是(StudentID, Subject, Score)，要转成“学生一行、各科成绩为列”的结构，核心是用聚合函数（MAX/MIN/SUM都可以，因为单个学生单科目只有一个分数，聚合结果不影响）包裹Score列，配合PIVOT实现转置：

静态列转置（已知所有科目）

如果科目固定，直接写死列名即可：

DECLARE @table TABLE (StudentID INT, Subject NVARCHAR(50), Score INT)
INSERT INTO @table VALUES 
(1, 'Math', 90),
(1, 'English', 85),
(1, 'Physics', 92),
(2, 'Math', 88),
(2, 'English', 79)

SELECT StudentID, [Math], [English], [Physics]
FROM @table
PIVOT (
    MAX(Score)  -- 单科目单分数，MAX/MIN效果一致
    FOR Subject IN ([Math], [English], [Physics])
) AS PivotResult

动态列转置（科目不固定，自动生成列）

如果科目是动态的，先自动获取所有科目名再拼接SQL：

DECLARE @table TABLE (StudentID INT, Subject NVARCHAR(50), Score INT)
INSERT INTO @table VALUES 
(1, 'Math', 90),
(1, 'English', 85),
(1, 'Physics', 92),
(2, 'Math', 88),
(2, 'English', 79),
(3, 'Chemistry', 87)

-- 拼接所有科目的带引号列名
DECLARE @subjects NVARCHAR(MAX)
SELECT @subjects = STRING_AGG(QUOTENAME(Subject), ', ') FROM (SELECT DISTINCT Subject FROM @table) AS sub

-- 拼接动态SQL语句
DECLARE @sql NVARCHAR(MAX)
SET @sql = '
SELECT StudentID, ' + @subjects + '
FROM @tbl
PIVOT (
    MAX(Score)
    FOR Subject IN (' + @subjects + ')
) AS PivotResult'

-- 执行动态SQL并传递表变量
EXEC sp_executesql @sql, N'@tbl TABLE (StudentID INT, Subject NVARCHAR(50), Score INT)', @tbl = @table

内容的提问来源于stack exchange，提问作者TCNJK