CODEWITHSUNDEEP
sqlsql-server
ASH
ASH

Reputation: 20302

UNION with all field names and nulls where names don't exist

I am fiddling around with this code to find all table names with all the fields that I need.

select t.name, c.name
from sys.columns c
join sys.tables t
on c.object_id = t.object_id
where c.name like '%ID%' 
     OR c.name like '%ID1%'
     OR c.name like '%ID2%'
     OR c.name like '%ID3%'
     OR c.name like '%ABC%'
     OR c.name like '%RTG%'
order by c.name

I am getting a couple hundred table names and field names. Basically, I'm trying to figure out the easiest way to get my results like this:

select col1, col2, col3, null as col4 from table1
union all
select col1, col2, col3, null as col4 from table2
union all
select col1, col2, col3, col4 from table3;

The challenge here is...I can type out the logic, but I'm dealing with hundreds of tables/fields, and It's really difficult to hard-code all of this logic. I am using SQL Server Azure. Thoughts? Ideas? Suggestions?

Upvotes: 0

Views: 89

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

I don't know if I really recommend this, but you can use a scoping trick so all the subqueries are basically the same:

select t.*
from (values (NULL, NULL, NULL, NULL)) v(a, b, c, d) cross apply
     (select a, b, c, d
      from table1
     ) t
union all
select t.*
from (values (NULL, NULL, NULL, NULL)) v(a, b, c, d) cross apply
     (select a, b, c, d
      from table2
     ) t
. . .

Basically, the subquery will select values from the table if they exist. Otherwise, it will fetch them from v.

EDIT:

Or as Martin points out:

select t.*
from (values (NULL, NULL, NULL, NULL)) v(a, b, c, d) cross apply
     ((select a, b, c, d from table1
      ) union all
      (select a, b, c, d from table2
      ) union all
      . . .
     ) t

Upvotes: 2

Related Questions