Search code examples
sqlsql-serversql-server-2012

Multiple repetitive tables

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.