CODEWITHSUNDEEP
sqlt-sqlnaming
Philip
Philip

Reputation: 11

Naming query ouput columns using a separate query T-sql

Im looking for a way to name columns using the top 1 selection from a separate query. 

select
   case when t.test=1 then 1 else 0 end  as select top 1 name from #names,
   case when t.test=2 then 1 else 0 end  as select top 1 name from #names2
from table t

In this example I want to name then columns by the top selection from several temporary columns. However this does not work. Does anyone have a suggestion on how to make this work?

Thank you in advance, Philip

Upvotes: 1

Views: 51

Answers (1)

Cee McSharpface
Cee McSharpface

Reputation: 8726

The SQL query engine does not evaluate expressions when it expects a column name or alias (any object identifier is expected to be literal).

You can construct your SELECT as a string where you inject the names from subqueries:

declare @stmt nvarchar(max)

set @stmt='
    select
        case when t.test=1 then 1 else 0 end as ['+(select top 1 name from #names)+'],
        case when t.test=2 then 1 else 0 end as ['+(select top 1 name from #names2)+']
    from table t'

exec(@stmt)

Security concern As the word "inject" already implies, this can be vulnerable to injection attacks. Use with caution, and only if you have control over the contents of the #names and #names2 tables - do not use this approach when those tables can contain user input.

Upvotes: 1

Related Questions