如何在SQL Server中用动态查询为各列别名追加指定单词

需求说明 #

基于临时表#temptbllist（存储要查询的表名）和#tempschema（存储表结构信息），生成动态SQL，将每个查询列的别名设置为PriorYear+原列名（例如ID→PriorYearID）。

解决方案（SQL Server 2017+） #

利用STRING_AGG高效拼接列列表，避免手动循环：

DECLARE @SQLSTR nvarchar(max) = '';

-- 生成每个目标表的查询语句
SELECT @SQLSTR = @SQLSTR + 
    'SELECT ' + 
    STRING_AGG(QUOTENAME(column_name) + ' AS ' + QUOTENAME('PriorYear' + column_name), ', ') + 
    ' FROM ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ';' + CHAR(13) + CHAR(10)
FROM #tempschema s
JOIN #temptbllist t ON s.table_name = t.tblname
GROUP BY s.schema_name, s.table_name;

-- 打印生成的SQL用于验证
PRINT @SQLSTR;

-- 确认无误后执行动态SQL
-- EXEC sp_executesql @SQLSTR;

兼容SQL Server 2016及以下版本 #

如果使用低版本SQL Server（无STRING_AGG），改用FOR XML PATH拼接列：

DECLARE @SQLSTR nvarchar(max) = '';

SELECT @SQLSTR = @SQLSTR + 
    'SELECT ' + 
    STUFF((
        SELECT ', ' + QUOTENAME(column_name) + ' AS ' + QUOTENAME('PriorYear' + column_name)
        FROM #tempschema s2
        WHERE s2.schema_name = s.schema_name AND s2.table_name = s.table_name
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 2, '') + 
    ' FROM ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ';' + CHAR(13) + CHAR(10)
FROM #tempschema s
JOIN #temptbllist t ON s.table_name = t.tblname
GROUP BY s.schema_name, s.table_name;

PRINT @SQLSTR;
-- EXEC sp_executesql @SQLSTR;

生成效果（针对测试数据） #

对于测试数据中指定的Facility表，生成的SQL如下：

SELECT [ID] AS [PriorYearID], [FacilityName] AS [PriorYearFacilityName], [County] AS [PriorYearCounty], [State] AS [PriorYearState], [ZipCode] AS [PriorYearZipCode] FROM [TestSchema].[Facility];

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