SQL Server : multiple repetitive tables

May 15, 2024

I’m querying results out of 2 tables, at a time, which has a repetitive pattern.

select 
    abc, xyz
from
    2010_Q1_result 
inner join 
    2010_Q1_run

(next comes 2010_Q2… inner join 2010_Q2_run all the way to 2024_Q1_result inner join 2024_Q1_run).

So far I have been changing the year and quarter manually in the query and pulling out results one by one.

How do I automate it to a single query?

>Solution :

You could consider pulling once with a UNION:

select 2010 as year, 'q1' as quarter, abc, xyz
from
2010_Q1_result 
inner join 2010_Q1_run
UNION ALL
select 2010 , 'q2' , abc, xyz
from
2010_Q2_result 
inner join 2010_Q2_run
UNION ALL
select 2010 , 'q3', abc, xyz
from
2010_Q3_result 
inner join 2010_Q3_run
UNION ALL
....

Once written that will spit out every record with columns denoting the year and column. You could toss that whole nasty thing in a view and then just SELECT * FROM my_view_that_I_made so you don’t have to write this every time you want to query.