none
Select statement return column with "NULL" value if the column does not exist?

    Question

  • I have couple of temp tables (even worse, some "tables" are results generated dynamically by a dynamic SQL in storedprocedure) which might have some common columns. Let's say they are something like

    TableA (Name, Age, Title)

    TableB(Name, Age)

    TableC(Age, Title)

     

    Is there a way to query them with one statement like

    select Name, Age, Title from TableName

    so if the table does not have certain column, the result will contain a column full of NULL value?

     

     

     

    Thanks




    • Edited by P.C.F. _ Tuesday, August 09, 2011 10:14 PM
    Tuesday, August 09, 2011 9:53 PM

Answers

  • To generate it dynamically, you would at least have to know the name of the tables.  Replace the list which appears twice in the SQL below.

    declare @sql nvarchar(max)
    select @sql = coalesce(@sql + ' union all ' + char(13), '') +
    	'select ' +
    	quotename(o.name, '''') + ' as TableName, ' +
    	stuff((cast((
    	select ',' + coalesce(quotename(c.name), 'null as ' + quotename(n.name))
    	from (
    		select distinct c.name
    		from syscolumns c
    		join sysobjects o on o.id=c.id
    		where o.name in ('tablea','tableb','tablec')
    		) n
    	left join syscolumns c on c.id=o.id and c.name=n.name
    	for xml path(''), root('fake')
    	) as xml)).value('/fake[1]', 'nvarchar(max)'),1,1,'')
    	+ ' from ' + QUOTENAME(o.name)
    from sysobjects o
    where o.name in ('tablea','tableb','tablec')
    order by o.name
    --print @sql
    exec (@sql)
    
    
    Uncomment the 2nd to last line to see the generated SQL statement.  If by "temp" table you mean table-variable or #temp, then I'm out of ideas.

    Tuesday, August 09, 2011 10:54 PM

All replies

  • You mean you want to combine all records from all 3 tables?

    select 'TableA' as TableName, Name, Age, Title from TableA
    union all
    select 'TableB', Name, Age, null from TableB
    union all
    select 'TableC', null, Age, Title from TableC
    order by Age

     

    • I threw in the first column to identify the source of the records. You can omit it if you prefer
    • The "Order by" clause applies not the the last SELECT, but across the entire result set

    Tuesday, August 09, 2011 9:56 PM
  • You mean you want to combine all records from all 3 tables?

     

    select 'TableA' as TableName, Name, Age, Title from TableA
    union all
    select 'TableB', Name, Age, null from TableB
    union all
    select 'TableC', null, Age, Title from TableC
    order by Age
    


     

    Kinda, but I don't know which columns the table might have, so I can't replace that columnname with null in the select...union all

    PC
    Tuesday, August 09, 2011 9:58 PM
  • You can generate your SQL statement dynamically, although this will be a bit tricky.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, August 09, 2011 10:11 PM
    Moderator
  • To generate it dynamically, you would at least have to know the name of the tables.  Replace the list which appears twice in the SQL below.

    declare @sql nvarchar(max)
    select @sql = coalesce(@sql + ' union all ' + char(13), '') +
    	'select ' +
    	quotename(o.name, '''') + ' as TableName, ' +
    	stuff((cast((
    	select ',' + coalesce(quotename(c.name), 'null as ' + quotename(n.name))
    	from (
    		select distinct c.name
    		from syscolumns c
    		join sysobjects o on o.id=c.id
    		where o.name in ('tablea','tableb','tablec')
    		) n
    	left join syscolumns c on c.id=o.id and c.name=n.name
    	for xml path(''), root('fake')
    	) as xml)).value('/fake[1]', 'nvarchar(max)'),1,1,'')
    	+ ' from ' + QUOTENAME(o.name)
    from sysobjects o
    where o.name in ('tablea','tableb','tablec')
    order by o.name
    --print @sql
    exec (@sql)
    
    
    Uncomment the 2nd to last line to see the generated SQL statement.  If by "temp" table you mean table-variable or #temp, then I'm out of ideas.

    Tuesday, August 09, 2011 10:54 PM
  • To generate it dynamically, you would at least have to know the name of the tables.  Replace the list which appears twice in the SQL below.

     

    declare @sql nvarchar(max)
    select @sql = coalesce(@sql + ' union all ' + char(13), '') +
    	'select ' +
    	quotename(o.name, '''') + ' as TableName, ' +
    	stuff((cast((
    	select ',' + coalesce(quotename(c.name), 'null as ' + quotename(n.name))
    	from (
    		select distinct c.name
    		from syscolumns c
    		join sysobjects o on o.id=c.id
    		where o.name in ('tablea','tableb','tablec')
    		) n
    	left join syscolumns c on c.id=o.id and c.name=n.name
    	for xml path(''), root('fake')
    	) as xml)).value('/fake[1]', 'nvarchar(max)'),1,1,'')
    	+ ' from ' + QUOTENAME(o.name)
    from sysobjects o
    where o.name in ('tablea','tableb','tablec')
    order by o.name
    --print @sql
    exec (@sql)
    
    
    Uncomment the 2nd to last line to see the generated SQL statement.  If by "temp" table you mean table-variable or #temp, then I'm out of ideas.

     

    Thanks a lot. Too bad, you are right, there's one "table" is a table variable.

    PC
    Tuesday, August 09, 2011 11:27 PM
  • Too bad, you are right, there's one "table" is a table variable.

    It is impossible to ask SQL Server to guess everything you want it to do. For some things, you just have to code it yourself. Your table variable is in memory only not backed by any system table/view, and it lives only in the procedural block in which you define it. Surely, at that point, you know what the columns should be to be able to construct the static SQL statement using the pattern shown?

    Tuesday, August 09, 2011 11:49 PM
  • Thanks guys. I figured out a way to take care of the in memory table variable. And then lookup columns in sysobjects like you did, Cyberkiwi. It's done.

    Again, thank you all for the help.


    PC
    Tuesday, August 16, 2011 6:00 AM