SQL Query for union table

Question

Hi,
I import data from 5 excel files. Each has different columns (totally out of 5 files there may be 60 columns)

I need to send this as single result set as datasource for SSRS report. What is the best way to create sql query.
For example

I have table1, table2, table3, table4, table 5

table1 has column1, column2
table 2 has column5, column6, column 7
table 3 has column8, column9 etc.

I need to union all these 60 columns and send as 1 result set to SSRS report.

Thank You

Answer 1

Hi @Spunny ，

It is hard to meet your requirement without common column.

Why not directly use power query in Excel to load the data to the same sheet?

You may refer to combine-data-from-multiple-workbooks.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Theoretically you can union the data in this manner:

select 'table1' as source, column1, column2, null as column5, null as column6, null as column7, null as column8, null as column9  
from table1  
union all  
select 'table2', null, null, column5, column6, column7, null, null  
from table2  
union all  
select 'table3', null, null, null, null, null, column8, column9  
from table3  

But it seems that you should avoid this.

Maybe you can build and use an XML or JSON.