SQL Puzzle - Calendar of Current Year

With this post, I want to share a SQL Puzzle to Generate the Complete Calendar of Current Year. As you can see in the desired output
 - Month names should not be repeated
 - For each month we should have a separate header.

The solution to generate the calendar of current year is also shared at the end of the post. I would suggest all to try to solve this problem before looking at the solution and please do post your solution in comment box. Enjoy solving the SQL Puzzle and happy SQL learning :)

Desired Output:

MONTH           SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
January                  01 02 03 04
                05 06 07 08 09 10 11
                12 13 14 15 16 17 18
                19 20 21 22 23 24 25
                26 27 28 29 30 31

MONTH           SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
February                          01
                02 03 04 05 06 07 08
                09 10 11 12 13 14 15
                16 17 18 19 20 21 22
                23 24 25 26 27 28 29

MONTH           SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
March           01 02 03 04 05 06 07
                08 09 10 11 12 13 14
                15 16 17 18 19 20 21
                22 23 24 25 26 27 28
                29 30 31

MONTH           SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
April                    01 02 03 04
                05 06 07 08 09 10 11
                12 13 14 15 16 17 18
                19 20 21 22 23 24 25
                26 27 28 29 30

MONTH           SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
May                            01 02
                03 04 05 06 07 08 09
                10 11 12 13 14 15 16
                17 18 19 20 21 22 23
                24 25 26 27 28 29 30
                31

MONTH           SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
June               01 02 03 04 05 06
                07 08 09 10 11 12 13
                14 15 16 17 18 19 20
                21 22 23 24 25 26 27
                28 29 30

MONTH           SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
July                     01 02 03 04
                05 06 07 08 09 10 11
                12 13 14 15 16 17 18
                19 20 21 22 23 24 25
                26 27 28 29 30 31

MONTH           SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
August                            01
                02 03 04 05 06 07 08
                09 10 11 12 13 14 15
                16 17 18 19 20 21 22
                23 24 25 26 27 28 29
                30 31

MONTH           SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
September             01 02 03 04 05
                06 07 08 09 10 11 12
                13 14 15 16 17 18 19
                20 21 22 23 24 25 26
                27 28 29 30

MONTH           SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
October                     01 02 03
                04 05 06 07 08 09 10
                11 12 13 14 15 16 17
                18 19 20 21 22 23 24
                25 26 27 28 29 30 31

MONTH           SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
November        01 02 03 04 05 06 07
                08 09 10 11 12 13 14
                15 16 17 18 19 20 21
                22 23 24 25 26 27 28
                29 30

MONTH           SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
December              01 02 03 04 05
                06 07 08 09 10 11 12
                13 14 15 16 17 18 19
                20 21 22 23 24 25 26
                27 28 29 30 31

Solution: To create the solution, I have used some SQL*Plus formatting options.

ngarg> BREAK ON MONTH
ngarg> BREAK ON MONTH SKIP PAGE
ngarg> COL MONTH FORMAT A15
ngarg>
ngarg> with curr_year as
  2  (
  3    select
  4      trunc(sysdate,'year') -1 + level dt
  5    from dual
  6      connect by level <=
  7        add_months(trunc(sysdate,'year'),12) - trunc(sysdate,'year')
  8  ),
  9  data as
 10  (
 11    select
 12      dt,
 13      to_char(dt,'d') d,
 14      sum(case when to_char(dt,'d') = 1 or to_char(dt,'dd') = 1 then 1 else 0 end) over (order by dt) week_no
 15    from
 16      curr_year
 17  )
 18  select
 19    to_char(min(dt),'Month') Month,
 20    max(case when d=1 then to_char(dt,'dd') end) sun,
 21    max(case when d=2 then to_char(dt,'dd') end) mon,
 22    max(case when d=3 then to_char(dt,'dd') end) tue,
 23    max(case when d=4 then to_char(dt,'dd') end) wed,
 24    max(case when d=5 then to_char(dt,'dd') end) thu,
 25    max(case when d=6 then to_char(dt,'dd') end) fri,
 26    max(case when d=7 then to_char(dt,'dd') end) sat
 27  from
 28    data
 29  group by week_no
 30  order by week_no;

Following are other SQL Puzzles which might interest you -
- SQL Puzzle - Consecutive Wins
- SQL Puzzle - Grouping Deals
- SQL Puzzle - Grouping Deals
- Graph Shortest Path Solution by SQL
- Diamond Shaped Star Pattern by SQL
- SQL Puzzle - Sorting Versions stored in Varchar2 Column
- SQL Puzzle - Issue Tracker - with Solution Approach
- SQL Puzzle - Transpose Rows and Shift Values among columns
- SQL Puzzle - Jobs taking more than 10 individual minutes in Parallel Jobs Environment
- SQL Interview Question Answers