Select columnValue if the column exists otherwise null

Question

I'm wondering if I can select the value of a column if the column exists and just select null otherwise. In other words I'd like to "lift" the select statement to handle the case when the column doesn't exist.

SELECT uniqueId
    ,  columnTwo
    ,  /*WHEN columnThree exists THEN columnThree ELSE NULL END*/ AS columnThree
FROM (subQuery) s

Note, I'm in the middle to solidifying my data model and design. I hope to exclude this logic in the coming weeks, but I'd really like to move beyond this problem right because the data model fix is a more time consuming endeavor than I'd like to tackle now.

Also note, I'd like to be able to do this in one query. So I'm not looking for an answer like

check what columns are on your sub query first. Then modify your query to appropriately handle the columns on your sub query.

Answer 1

You cannot do this with a simple SQL statement. A SQL query will not compile unless all table and column references in the table exist.

You can do this with dynamic SQL if the "subquery" is a table reference or a view.

In dynamic SQL, you would do something like:

declare @sql nvarchar(max) = '
SELECT uniqueId, columnTwo, '+
    (case when exists (select *
                       from INFORMATION_SCHEMA.COLUMNS 
                       where tablename = @TableName and
                             columnname = 'ColumnThree' -- and schema name too, if you like
                      )
          then 'ColumnThree'
          else 'NULL as ColumnThree'
     end) + '
FROM (select * from '+@SourceName+' s
';

exec sp_executesql @sql;

For an actual subquery, you could approximate the same thing by checking to see if the subquery returned something with that column name. One method for this is to run the query: select top 0 * into #temp from (<subquery>) s and then check the columns in #temp.

EDIT:

I don't usually update such old questions, but based on the comment below. If you have a unique identifier for each row in the "subquery", you can run the following:

select t.. . .,  -- everything but columnthree
       (select column3   -- not qualified!
        from t t2
        where t2.pk = t.pk
       ) as column3
from t cross join
     (values (NULL)) v(columnthree);

The subquery will pick up column3 from the outer query if it doesn't exist. However, this depends critically on having a unique identifier for each row. The question is explicitly about a subquery, and there is no reason to expect that the rows are easily uniquely identified.