Is there a function/workaround to combining results from more than one table

I am using a dataset of a fictional bike sharing service. The data altogether emcompasses 12 months of data, split into 12 tables, one for each month. The columns and datatypes are consistent, and I am focusing on two columns – member-type and ride_length. The member_type column only has two values, ‘member’ or ‘casual’. I want to compare the average ride length between member and casual riders for each month but have the results displayed on one table.

The results should look like this:

member_type | january | february | march
member | 100 | 50 | 30
casual | 60 | 45 | 25

The one solution I can think of is to combine all the tables into one big table, create a month column and go from there, but I don’t want to do that if I can help it. Thank you in advance.

Answer

Use a CTE to build the table you don’t want to create on the fly.

with combined as (
   select 1 as month, T.* from jan_tbl T union all
   select 2, T.* from feb_tbl T union all
...
)
select  from combined