SQL Server中如何在AS后使用变量定义查询列名？

DECLARE @MyVariable VARCHAR(max);
SET @MyVariable = 'So,So_Line,So_other';

SELECT CONCAT(So, t.x) AS @MyVariable
FROM [test].[dbo].[foo] 
CROSS JOIN (VALUES('-1'),('-2'),('-3'),('-4'),('-5')) t(x) 
WHERE So = 'ABC'

UNION

SELECT CONCAT(So, t.x) AS @MyVariable
FROM [test].[dbo].[foo] 
CROSS JOIN (VALUES('-1'),('-2')) t(x) 
WHERE So = 'BDC'

SELECT CONCAT(So, t.x) AS So, So_Line, So_other
FROM [test].[dbo].[foo] 
CROSS JOIN (VALUES('-1'),('-2'),('-3'),('-4'),('-5')) t(x) 
WHERE So = 'ABC'

UNION 

SELECT CONCAT(So, t.x) AS So, So_Line, So_other 
FROM [test].[dbo].[foo] 
CROSS JOIN (VALUES('-1'),('-2')) t(x) 
WHERE So = 'BDC'

问题说明 #

实际处理的表包含约300列，无法手动逐个列写列名，尝试用变量定义列名时报错，需要解决批量列名的动态生成问题。

解决方案 #

SQL Server无法直接用变量替换SELECT的列列表，必须通过动态SQL实现，核心思路是自动拼接列名与完整SQL语句后执行：

具体实现代码 #

-- 定义So值对应的后缀数量映射
DECLARE @SuffixMap TABLE (SoValue VARCHAR(10), SuffixCount INT);
INSERT INTO @SuffixMap VALUES ('ABC',5),('BDC',2);

-- 自动获取表的所有列名，拼接SELECT子句：So列处理为带后缀的格式，其他列直接保留原列名
DECLARE @SelectColumns NVARCHAR(MAX);
-- SQL Server 2017及以上版本用STRING_AGG
SELECT @SelectColumns = STRING_AGG(
    CASE WHEN COLUMN_NAME = 'So' THEN 'CONCAT(f.So, s.Suffix) AS So' ELSE 'f.' + COLUMN_NAME END,
    ', '
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'foo';

-- 拼接完整动态SQL
DECLARE @DynamicSQL NVARCHAR(MAX);
SET @DynamicSQL = N'
WITH Suffixes AS (
    SELECT SoValue, ''-'' + CAST(n AS VARCHAR) AS Suffix
    FROM @SuffixMap sm
    CROSS JOIN (SELECT TOP(sm.SuffixCount) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n FROM sys.all_columns) nums
)
SELECT ' + @SelectColumns + '
FROM [test].[dbo].[foo] f
JOIN Suffixes s ON f.So = s.SoValue
ORDER BY f.So, s.Suffix;
';

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL, N'@SuffixMap TABLE (SoValue VARCHAR(10), SuffixCount INT)', @SuffixMap = @SuffixMap;

低版本兼容（SQL Server 2016及以下） #

如果你的SQL Server版本不支持STRING_AGG，替换列名拼接部分为：

SELECT @SelectColumns = STUFF(
    (SELECT ', ' + CASE WHEN COLUMN_NAME = 'So' THEN 'CONCAT(f.So, s.Suffix) AS So' ELSE 'f.' + COLUMN_NAME END
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'foo'
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 2, ''
);

代码说明 #

1. 用INFORMATION_SCHEMA.COLUMNS自动读取表的所有列名，无需手动输入300列
2. 用表变量@SuffixMap统一管理不同So值对应的后缀数量，后续修改规则只需更新该变量
3. 用CTE生成所有需要的后缀，替代原有的UNION写法，逻辑更简洁易维护
4. 通过sp_executesql执行动态SQL，保证语句的安全性与灵活性

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