CODEWITHSUNDEEP
sqlsql-servert-sql
Ezra Bailey
Ezra Bailey

Reputation: 1433

TSQL Subquery for Column Names?

Is it possible to use a set of query results for column names in a select statement?

Example, I have a table named TableA:

Column:    Type:
KeyOne     nvarchar(5)
KeyTwo     nvarchar(5)

TableB is another table, whose column names might be stored in TableA.

Suppose TableB is like this:

Column:    Type:
Val1       int
Val2       int

Is there any way I could do a query like this to get the columns?

SELECT (select TOP 1 KeyOne, KeyTwo FROM TableA)
FROM TableB

Another example using strings would be like this:

SELECT (select 'Val1', 'Val2')
FROM TableB

Is this possible in any way without concatenated SQL?

Upvotes: 0

Views: 1111

Answers (2)

Eralper
Eralper

Reputation: 6612

You can read table column names dynamically from sys.columns system views or using other management views

select name from sys.columns where object_id = object_id(N'TableName')

Then by creating a dynamic SQL query you can create your own select

Upvotes: 1

Hart CO
Hart CO

Reputation: 34774

Unfortunately you can only do this with dynamic SQL, but it's pretty straightforward:

DECLARE @cols VARCHAR(MAX) = (SELECT TOP 1 KeyOne+','+KeyTwo FROM TableA)
       ,@sql VARCHAR(MAX) 
SET @sql = 'SELECT '+@cols+' FROM TableB'
EXEC (@sql)

Upvotes: 4

Related Questions