Multiple table outputs based on one query-CodePudding

I have one table that has 12 conditions (months). I need to produce 12 outputs (tables) for select statements where the only thing that changes is that one condition (month).

Example:

SELECT * FROM table WHERE month = 01;
SELECT * FROM table WHERE month = 02;
SELECT * FROM table WHERE month = 03;

etc.

In reality, the code is pretty large and chunky, I don't feel like copy pasting it numerous times just to change one condition.

Is there a way to write one statement to produce different outputs based on changing condition?

It looks pretty basic to me, but I can't find the answer.

CodePudding user response：

It doesn't sound like a good approach, but without a fuller picture of the task it's hard to say.

What you can do is generate a list of numbers and use it to produce your required statements, such as:

with months as (
    select 1 m
    union all
    select m   1 from months
    where m<12
)
select Concat('select * from table where month = ', m, ';')
from months

Depending on your requirements and RDBMS you can either use them directly in your IDE or dynamically execute.

CodePudding user response：

In Oracle, you could use hierarchical query to generate months; something like this:

SQL> select empno,
  2         ename,
  3         job,
  4         to_char(hiredate, 'fmMonth', 'nls_date_language = english') month
  5  from emp
  6  where extract(month from hiredate) in (select level from dual
  7                                         connect by level <= 12
  8                                        )
  9  order by extract (month from hiredate);

     EMPNO ENAME      JOB       MONTH
---------- ---------- --------- ------------------------------------
      7876 ADAMS      CLERK     January
      7934 MILLER     CLERK     January
      7499 ALLEN      SALESMAN  February
      7521 WARD       SALESMAN  February
      7566 JONES      MANAGER   April
      7698 BLAKE      MANAGER   May
      7782 CLARK      MANAGER   June
      7844 TURNER     SALESMAN  September
      7654 MARTIN     SALESMAN  September
      7839 KING       PRESIDENT November
      7788 SCOTT      ANALYST   December
      7369 SMITH      CLERK     December
      7900 JAMES      CLERK     December
      7902 FORD       ANALYST   December

14 rows selected.

SQL>

CodePudding user response：

Can be as simple as left/right join to cte months

declare @table table (mm smallint,somedata varchar(20))
insert into @table
values 
(1,'some data for 1')
,(3,'some data for 3')
,(5,'some data for 5')


;with cte_months as (
    select 1 mm
    union all
    select mm   1 from cte_months
    where mm<12
)
select c.mm,t.somedata from @table t
right  join cte_months c on t.mm = c.mm