CODEWITHSUNDEEP
sqlsql-server
IAMTubby
IAMTubby

Reputation: 1687

mentioning column names dynamically in SQL server

I would like to select certain columns of my table. However, instead of writing them as part of my select statement, I would like to populate it dynamically.

Say, I would like to select all columns starting with the word 'Subsequent' of the table Mytable. I am able to select columns as follows

select name from sys.columns where object_id=OBJECT_ID('Mytable') and name like 'Subsequent%'

I was thinking that mentioning it as a subquery as follows would work, but I wasn't able to get it to work.

select top 100 (select name from sys.columns where object_id=OBJECT_ID('Mytable') and name like 'Subsequent%') from Mytable

Upvotes: 0

Views: 48

Answers (1)

jpw
jpw

Reputation: 44911

You need to use dynamic sql to inject the columns into the select list of the statement. This is one way to do it:

declare @Table SYSNAME;
declare @cols nvarchar(max);
declare @sql nvarchar(max); 

 SET @Table = N'TableName';

select @cols = stuff(
    (select ',' + quotename(name)   
     from sys.columns 
     where object_id=object_id(@Table) 
     and name like 'Subsequent%'
     for xml path(''), type)
    .value('.', 'nvarchar(max)'),1,1,'') 

set @sql = N'select ' + @cols + N' from '+ QUOTENAME(@Table)
exec sp_executesql @sql

Upvotes: 3

Related Questions