CODEWITHSUNDEEP
sql-servert-sql
Rohit
Rohit

Reputation: 3638

How can I prefix all column names in a T-SQL function?

I have a table T with 2 columns C1 and C2

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.

It is possible?

I want something like

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

Upvotes: 0

Views: 2384

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

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)

Upvotes: 1

Alex K.
Alex K.

Reputation: 175956

As its for a known number of columns you can construct the SQL string with the alias and exec() it;

declare @prefix varchar(32) = 'prefix_'
declare @sql    nvarchar(1024) 

set @sql = 'select C1 as [' + @prefix + 'C1], C2 as [' + @prefix + 'C2] from T'
exec(@sql)

Upvotes: 0

Related Questions