Oracle: Analytics: How to Sort Using Some Column Names

Question

I have a query same as below.

select organization_name, is_manager, is_vendor, is_engineer
from table_org_roles;

Example data:

organization_name|is_manager|is_vendor|is_engineer
---------------------------------------------------
Org_A            |Yes       |No       |No         
Org_B            |No        |Yes      |No         
Org_C            |No        |No       |Yes
Org_D            |Yes       |No       |Yes

However I wanted to make a SQL query that will be ordered by the the role name ascended which is the column name. How can I make query that will display like below:

Roles(ASC) | Organization
-------------------------
is_engineer| Org_C
is_engineer| Org_D
is_manager | Org_A
is_manager | Org_D
is_vendor  | Org_B

Is it possible to make such query using Analytics? Thank you very much in advance.

Answer 1

Hi this can be done with the help of an UNPIVOT operation as well. see below.

   create table test_stack
(
organization_name varchar2(100),
is_manager varchar2(10),
is_vendor varchar2(20),
is_engineer varchar2(20)
);
/
insert into test_stack
select 'org_A','Yes','No','No' from dual
union all
select 'org_B','No','Yes','No' from dual
union all
select 'org_C','No','No','Yes' from dual
union all
select 'org_D','Yes','No','Yes' from dual; 




select organization_name,roles
    from test_stack
    unpivot include nulls ( existence for Roles in ( IS_MANAGER as 'IS_MANAGER',IS_VENDOR as 'IS_VENDOR',IS_ENGINEER as 'IS_ENGINEER'))
    where existence ='Yes';