Select column names with value of 1-CodePudding

I have an Oracle table set up like this:

MyTable
MENU  ROLE1  ROLE2  ROLE3
MENU1 1      0      1
MENU2 0      1      0
MENU3 1      1      1

I need a query that will give me the columns names with value 1 for a specific menu row.

select column_name from myTable where menu = 'MENU1'

The query should produce ROLE1,ROLE3

CodePudding user response：

How about a function that loops through all columns in MYTABLE (except MENU), checks their values and - if 1 is returned - adds that column into a collection.

SQL> create or replace function frole (par_menu in varchar2) return sys.odcivarchar2list
  2  is
  3    l_role mytable.role1%type;
  4    retval sys.odcivarchar2list := sys.odcivarchar2list();
  5  begin
  6    for cur_r in (select column_name, column_id
  7                  from user_tab_columns
  8                  where table_name = 'MYTABLE'
  9                    and column_name <> 'MENU')
 10    loop
 11      execute immediate 'select ' || cur_r.column_name || ' from mytable ' ||
 12                        ' where menu = :a' into l_role using par_menu;
 13      if l_role = 1 then
 14         retval.extend;
 15         retval(retval.count) := cur_r.column_name;
 16      end if;
 17    end loop;
 18    return retval;
 19  end;
 20  /

Function created.

Sample data:

SQL> select * From mytable;

MENU       ROLE1      ROLE2      ROLE3
----- ---------- ---------- ----------
MENU1          1          0          1
MENU2          0          1          0
MENU3          1          1          1

Testing:

SQL> select * From frole('MENU1');

COLUMN_VALUE
--------------------------------------------------------------------------------
ROLE1
ROLE3

SQL> select * From frole('MENU2');

COLUMN_VALUE
--------------------------------------------------------------------------------
ROLE2

SQL> select * From frole('MENU3');

COLUMN_VALUE
--------------------------------------------------------------------------------
ROLE1
ROLE2
ROLE3

SQL>

CodePudding user response：

You can create 3 views for the 3 roles.

create table MyTable(
Menu int,
Role1 int,
Role2 int,
Role3 int,
Menu1 int, 
Menu2 int,
Menu3 int);

✓

insert into MyTable values(1,2,3,4,5,6,7);

1 rows affected

create view Menu1 as
select Menu, Role1, Menu1 from MyTable;

✓

select * from Menu1;

MENU | ROLE1 | MENU1
---: | ----: | ----:
   1 |     2 |     5

db<>fiddle here