Multiple table outputs based on one query

Question

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.

T-SQL and PL/SQL are completely different dialects, used by complete different RDBMS (SQL Server and Oracle respectively). Please only tag what you are really using. — Thom A, Dec 06 '21 at 21:43
Why not just use parametrised queries, such as with a stored procedure..? Then you can call the same query 12 times, changing the parameter and that's it. — Thom A, Dec 06 '21 at 21:44
I know it sounds weird, but Iam using both RDBMS respectively, that's why I asked the same question for both. I was thinking of using SPs but I was hoping there is a way to loop select statements somehow because occasionaly I need to change the parameter a lot more than just 12 times — Sqrrrl, Dec 06 '21 at 21:59

score 0 · Answer 1 · answered Dec 06 '21 at 21:48

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.

score 0 · Answer 2 · answered Dec 06 '21 at 21:50

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>

score 0 · Answer 3 · answered Dec 06 '21 at 21:54

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

Multiple table outputs based on one query

3 Answers3