如何在SQL中筛选并查询以'UDF_%'为前缀的列？

我使用的JT_Employee表包含114列，需要查询所有以UDF_%为前缀的列，但尝试多种方法都没达到预期效果：

之前的失败尝试 #

• 仅获取列名（行形式展示）：通过INFORMATION_SCHEMA查到了目标列名，但结果是每行一个列名，无法直接用来查询数据

Select c.Column_Name
from INFORMATION_SCHEMA.columns as C
where c.Table_Name = 'JT_Employee'
    and c.COLUMN_NAME like 'UDF_%'

• 子查询方式执行失败：试图把列名查询作为子查询放在SELECT里，直接报错

Select
    (
    Select c.Column_Name
        from INFORMATION_SCHEMA.columns as C
    where c.Table_Name = 'JT_Employee'
        and c.COLUMN_NAME like 'UDF_%'
    )
From JT_Employee

• 错误使用等于号匹配前缀：用=代替LIKE，导致匹配不到任何列

Select column_name, table_name
from INFORMATION_SCHEMA.Columns
Where table_name in ('JT_Employee')
and column_name = 'UDF_%'

• 拼接列名但无法直接使用：用STUFF拼接出了列名字符串，但没法直接作为SELECT的列列表使用

Select Distinct
    Stuff((Select c.Column_Name +', '
    from INFORMATION_SCHEMA.columns as C
    where c.Table_Name = 'JT_Employee'
        and c.COLUMN_NAME like 'UDF_%'
    FOR XML PATH ('')),1,0,'')

期望效果 #

需要得到仅包含UDF_F_Name、UDF_L_Name、UDF_Hire Date等前缀列的结果集，示例如下：

原始数据示例 #

期望结果 #

解决方法 #

要实现动态查询这些列，需要使用动态SQL，把拼接好的列名字符串作为查询语句的一部分执行：

DECLARE @Columns NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

-- 拼接UDF前缀的列名（适用于SQL Server 2017及以上版本）
SELECT @Columns = STRING_AGG(c.Column_Name, ', ')
FROM INFORMATION_SCHEMA.columns as C
WHERE c.Table_Name = 'JT_Employee'
    AND c.COLUMN_NAME LIKE 'UDF_%'

-- 构建动态查询语句
SET @SQL = 'SELECT ' + @Columns + ' FROM JT_Employee'

-- 执行动态SQL
EXEC sp_executesql @SQL

注意：如果你的SQL Server版本低于2017，STRING_AGG不可用，可以用STUFF+XML PATH方式拼接列名：

SELECT @Columns = STUFF((SELECT ', ' + c.Column_Name
                         FROM INFORMATION_SCHEMA.columns as C
                         WHERE c.Table_Name = 'JT_Employee'
                             AND c.COLUMN_NAME LIKE 'UDF_%'
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

执行上述动态SQL后，就能直接得到所有UDF前缀列的数据。

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