Get range of columns with specific value in Oracle

I wasn’t sure how to title this question, but I’ve got an Oracle table that has a column for each day of the week. These represent the days a business is open. The values in these columns is either ‘Y’ or ‘N’. This is how the table was setup years ago and I am unable to change it.

ID | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY | SUNDAY
1      Y         Y          Y          Y          Y        N          N
2      N         Y          Y          Y          Y        Y          N

I need to be able to query this table and get results like the following, but am not sure where to even start.

ID  |     DAYS_OPEN
1      MONDAY - FRIDAY
2      TUESDAY - SATURDAY

Is this even possible in a query or will I need to use a function? If a function would make it easier, I’d be fine with that solution as well.

Answer

Found easier solution:

replace(
      trim( 
        '.' from
        replace(replace(replace(replace(replace(replace(replace(
            regexp_replace(
                   decode(MONDAY    ,'Y','1','.') ||
                   decode(TUESDAY   ,'Y','2','.') || 
                   decode(WEDNESDAY ,'Y','3','.') || 
                   decode(THURSDAY  ,'Y','4','.') || 
                   decode(FRIDAY    ,'Y','5','.') || 
                   decode(SATURDAY  ,'Y','6','.') || 
                   decode(SUNDAY    ,'Y','7','.')                   
                  ,'(d)d*(d)','1-2')
           ,'1','MONDAY')
           ,'2','TUESDAY')
           ,'3','WEDNESDAY')
           ,'4','THURSDAY')
           ,'5','FRIDAY')
           ,'6','SATURDAY')
           ,'7','SUNDAY')
        ),'.',',')

Full example:

with t(ID, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY) as (
   select 1,'Y', 'Y', 'Y', 'Y', 'Y' ,'N', 'N' from dual union all
   select 2,'N', 'Y', 'Y', 'Y', 'Y' ,'Y', 'N' from dual union all
   select 3,'Y', 'N', 'Y', 'Y', 'N' ,'Y', 'Y' from dual
)
select
   id
  ,replace(
      trim( 
        '.' from
        replace(replace(replace(replace(replace(replace(replace(
            regexp_replace(
                   decode(MONDAY    ,'Y','1','.') ||
                   decode(TUESDAY   ,'Y','2','.') || 
                   decode(WEDNESDAY ,'Y','3','.') || 
                   decode(THURSDAY  ,'Y','4','.') || 
                   decode(FRIDAY    ,'Y','5','.') || 
                   decode(SATURDAY  ,'Y','6','.') || 
                   decode(SUNDAY    ,'Y','7','.')                   
                  ,'(d)d*(d)','1-2')
           ,'1','MONDAY')
           ,'2','TUESDAY')
           ,'3','WEDNESDAY')
           ,'4','THURSDAY')
           ,'5','FRIDAY')
           ,'6','SATURDAY')
           ,'7','SUNDAY')
        ),'.',',')
     as sch
from t;

Result:

        ID SCH
---------- ------------------------------------------------------------
         1 MONDAY-FRIDAY
         2 TUESDAY-SATURDAY
         3 MONDAY,WEDNESDAY-THURSDAY,SATURDAY-SUNDAY

Edit: expanding on the original answer to apply the same process to months of the year. I had to wrap the month number in square brackets in the decode. Then in the regexp_replace, I specified a non-matching list (period and whitespace).

Here’s the full example:

with t(ID, JANUARY, FEBRUARY, MARCH, APRIL, MAY, JUNE, JULY, AUGUST, SEPTEMBER, OCTOBER, NOVEMBER, DECEMBER ) as (
   select 1,'Y', 'Y', 'Y', 'Y', 'Y' ,'N', 'N', 'Y','Y','Y','Y','Y' from dual union all
   select 2,'Y', 'Y', 'Y', 'Y', 'N' ,'N', 'N', 'N','Y','Y','Y','Y' from dual union all
   select 3,'N', 'N', 'Y', 'N', 'N' ,'Y', 'Y', 'N','N','N','Y','N' from dual union all
   select 4,'Y', 'N', 'Y', 'N', 'Y' ,'N', 'Y', 'N','Y','N','Y','N' from dual
)
select
   id,
regexp_replace(
          trim( '.' from
            replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
            regexp_replace(
                       decode(JANUARY    ,'Y','01','.') ||
                       decode(FEBRUARY   ,'Y','02','.') || 
                       decode(MARCH      ,'Y','03','.') || 
                       decode(APRIL      ,'Y','04','.') || 
                       decode(MAY        ,'Y','05','.') || 
                       decode(JUNE       ,'Y','06','.') || 
                       decode(JULY       ,'Y','07','.') || 
                       decode(AUGUST     ,'Y','08','.')|| 
                       decode(SEPTEMBER  ,'Y','09','.')|| 
                       decode(OCTOBER    ,'Y','10','.')|| 
                       decode(NOVEMBER   ,'Y','11','.')|| 
                       decode(DECEMBER   ,'Y','12','.')                  
                      ,'(dd)d*(dd)','1-2')
               ,'01','Jan')
               ,'02','Feb')
               ,'03','Mar')
               ,'04','Apr')
               ,'05','May')
               ,'06','Jun')
               ,'07','Jul')
               ,'08','Aug')
               ,'09','Sep')
               ,'10','Oct')
               ,'11','Nov')
               ,'12','Dec')
              )
            ,'.+',', ') MNTHS
            from t;

and the results:

ID  MNTHS
1   Jan-May, Aug-Dec 
2   Jan-Apr, Sep-Dec 
3   Mar, Jun-Jul, Nov
4   Jan, Mar, May, Jul, Sep, Nov