如何在 T-SQL 函数中为所有列名添加前缀?

问题描述：

我有一个包含 2 列 C1 和 C2 的表 T

I have a table T with 2 columns C1 and C2

我想要一个返回 T 但在所有列名前加上PREFIX_"前缀的函数.

I want a function that returns T but after prefixing all column names with "PREFIX_".

前缀是传递给函数的动态字符串.

The prefix is a dynamic string passed to the function.

有可能吗?

我想要类似的东西

SELECT C1 AS @prefix + 'C1', 
    C2 AS @prefix + 'C2' 
FROM T

答

即使您不知道列也可以这样做.

This will do it even when you don't know the columns already.

declare @prefix varchar(20)
declare @sqltext nvarchar(500)
set @prefix = 'prefix_'

declare @columnnames varchar(500)

select @columnnames = coalesce(@columnnames, '')+',' +column_name + ' '+ @prefix+column_name 
from INFORMATION_SCHEMA.COLUMNS a where table_name = 't' and table_schema = 'dbo'

set @sqltext = 'select '+stuff(@columnnames,1,1,char(0))+' from t'

exec(@sqltext)

如何在 T-SQL 函数中为所有列名添加前缀?

相关推荐