I am dealing with the situation in sql server where a database contains the multiple tables with similar table name (ex total_0122, total_0622) and same column. I wanted to calculate the percentage difference from all those tables by taking particular columns data. My approch is
- list all the tables present in the database with name like %Total_%
- Then concatenate/join the all the tables together
- Read the table content
- lastly find the percentage diffrence.
I am able to list all the tables present in the database and able to concatenate it which is step 1 and 2. for which i have performed
''' SELECT sys.columns.name AS ColumnName, tables.name AS TableName FROM sys.columns JOIN sys.tables ON sys.columns.object_id = tables.object_id WHERE sys.tables.name LIKE '%total_%
which gives me an output as
ColumnName TableName email total_0122 country total_0122 revenue total_0122 email total_0622 country total_0622 revenue total_0622
I am not able to figure out how to read those tables into one table along with the column values (email, country, revenue...) I am new sql. Any help will be appreciated. Please advice.