Select columnValue if the column exists otherwise null

Solution 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.

Solution 2:

As others already suggested, the sane approach is to have queries that meet your table design.

There is a rather exotic approach to achieve what you want in (pure, not dynamic) SQL though. A similar problem was posted at DBA.SE: How to select specific rows if a column exists or all rows if a column doesn't but it was simpler as only one row and one column was wanted as result. Your problem is more complex so the query is more convoluted, to say the least. Here is, the insane approach:

; WITH s AS
  (subquery)                                    -- subquery
SELECT uniqueId
    ,  columnTwo
    ,  columnThree =
       ( SELECT ( SELECT columnThree 
                  FROM s AS s2
                  WHERE s2.uniqueId = s.uniqueId
                ) AS columnThree
         FROM (SELECT NULL AS columnThree) AS dummy
       )
FROM s ;

It also assumes that the uniqueId is unique in the result set of the subquery.

Tested at SQL-Fiddle

And a simpler method which has the additional advantage that allows more than one column with a single subquery:

SELECT s.*     
FROM
    ( SELECT NULL AS columnTwo,
             NULL AS columnThree,
             NULL AS columnFour
    ) AS dummy 
  CROSS APPLY
    ( SELECT 
          uniqueId,
          columnTwo,
          columnThree,
          columnFour
      FROM tableX
    ) AS s ;

The question has also been asked at DBA.SE and has been answered by @Andriy M (using CROSS APPLY too!) and Michael Ericsson (using XML):
Why can't I use a CASE statement to see if a column exists and not SELECT from it?

Solution 3:

you can use dynamic SQL.

first you need to check exist column and then create dynamic query.

DECLARE @query NVARCHAR(MAX) = '
SELECT FirstColumn, SecondColumn, '+
  (CASE WHEN exists (SELECT 1 FROM syscolumns 
  WHERE name = 'ColumnName' AND id = OBJECT_ID('TableName'))
      THEN 'ColumnName'
      ELSE 'NULL as ThreeColumn'
   END) + '
FROM TableName'

EXEC sp_executesql @query;