Oracle SQL - creating a loop (oracle11g)

Question

Please can you explain how I could create a loop function to give me the same result as the following,

select  to_char(add_months(trunc(sysdate,'MM'),- 0),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),- 0),'YYYY') today,
        to_char(add_months(trunc(sysdate,'MM'),- 1),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),- 1),'YYYY') date_1,
        to_char(add_months(trunc(sysdate,'MM'),- 2),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),- 2),'YYYY') date_2,
        to_char(add_months(trunc(sysdate,'MM'),- 3),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),- 3),'YYYY') date_3,
        to_char(add_months(trunc(sysdate,'MM'),- 4),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),- 4),'YYYY') date_4,
        to_char(add_months(trunc(sysdate,'MM'),- 5),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),- 5),'YYYY') date_5,
        to_char(add_months(trunc(sysdate,'MM'),- 6),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),- 6),'YYYY') date_6,
        to_char(add_months(trunc(sysdate,'MM'),- 7),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),- 7),'YYYY') date_7,
        to_char(add_months(trunc(sysdate,'MM'),- 8),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),- 8),'YYYY') date_6,
        to_char(add_months(trunc(sysdate,'MM'),- 9),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),- 9),'YYYY') date_9,
        to_char(add_months(trunc(sysdate,'MM'),-10),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),-10),'YYYY') date_10,
        to_char(add_months(trunc(sysdate,'MM'),-11),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),-11),'YYYY') date_11
from dual

I want the results to populate in to columns, like ...

enter image description here

I want to use this go back to Jan 2000 but, don't really want to repeat these lines. Also, every month I would have to add another line to cover the new month. I am wondering if there is a LOOP function in SQL similar to a DO ... LOOP UNTIL in Excel.

Thanks

SMORF

What is wrong with the code you have? What do you mean by "loop function"? — Gordon Linoff
– Gordon Linoff, Commented Oct 27, 2014 at 13:52
Hi, I want to use this go back to Jan 2000 but, don't really want to repeat these lines. Also, every month I would have to add another line to cover the new month. I am wondering if there is a LOOP function in SQL similar to a DO ... LOOP UNTIL in Excel — SMORF
– SMORF, Commented Oct 27, 2014 at 13:56
SQL and PL/SQL are diffeent engines. If you can achieve something in SQL, there is no reason to do it in PL/SQL. — Lalit Kumar B
– Lalit Kumar B, Commented Oct 27, 2014 at 13:59

tbone · Accepted Answer · 2014-10-27 14:19:14Z

2

I think what you want is:

select * from (
select level-1 as num, 
to_char(add_months(trunc(sysdate,'MM'),- (level-1)),'MM')||'-'||to_char(add_months(trunc(sysdate,'MM'),- (level-1)),'YYYY') as dte
from dual
connect by level <= 12
)
pivot  (
max(dte) as "DATE"
for num in (0 as "CURRENT", 1 as "1", 2 as "2", 3 as "3", 4 as "4", 5 as "5",6 as "6",7 as "7",8 as "8",9 as "9",10 as "10", 11 as "11"))

Output:

CURRENT_DATE    1_DATE  2_DATE  3_DATE  4_DATE  5_DATE  6_DATE  7_DATE  8_DATE  9_DATE  10_DATE 11_DATE
10-2014 09-2014 08-2014 07-2014 06-2014 05-2014 04-2014 03-2014 02-2014 01-2014 12-2013 11-2013

answered Oct 27, 2014 at 14:19

tbone

15.5k3 gold badges37 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SMORF Over a year ago

Thanks tbone, this is perfect

Gordon Linoff · Accepted Answer · 2014-10-27 14:13:11Z

This is too long for a comment.

A SQL query has a fixed number of columns. You could do what you want if you had a separate row for each month. I would write this as something like this:

with n as (
      select level - 1 as n
      from dual
      connect by level <= months_between(sysdate, date '2000-01-01')
select to_char(add_months(sysdate, n.n), 'YYYY-MM')
from n;

(Note: I changed the format from MM-YYYY to YYYY-MM because I much prefer the latter, but you can keep MM-YYYY if you like.)

Making these columns is a problem for SQL. A SQL query has a fixed number of columns. To have a variable number, you will need to use dynamic SQL. I would suggest that you google "Oracle dynamic pivot" to get some ideas on how to solve this.

Collectives™ on Stack Overflow

Oracle SQL - creating a loop (oracle11g)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related