Convert SQL query to pivot

Question

I can't seem able to get the logic to convert the following query to a pivot SQL. My table has 20 columns with roles on them, I'd like to convert those columns into rows so, when exported to Excel, I can filter on a single column since the values can be the same on the 20 columns. So far what I've done is convert the 20 columns into a single one and then split that single one into rows:

select      distinct TASKID,
          regexp_substr(t.roles,'[^|]+', 1, lines.column_value) as role
from        (
            select    TASKID,
                      TRIM(ROLE1) || '|' ||
                      TRIM(ROLE2) || '|' ||
                      TRIM(ROLE3) || '|' ||
                      TRIM(ROLE4) || '|' ||
                      TRIM(ROLE5) || '|' ||
                      TRIM(ROLE6) || '|' ||
                      TRIM(ROLE7) || '|' ||
                      TRIM(ROLE8) || '|' ||
                      TRIM(ROLE9) || '|' ||
                      TRIM(ROLE10) || '|' ||
                      TRIM(ROLE11) || '|' ||
                      TRIM(ROLE12) || '|' ||
                      TRIM(ROLE13) || '|' ||
                      TRIM(ROLE14) || '|' ||
                      TRIM(ROLE15) || '|' ||
                      TRIM(ROLE16) || '|' ||
                      TRIM(ROLE17) || '|' ||
                      TRIM(ROLE18) || '|' ||
                      TRIM(ROLE19) || '|' ||
                      TRIM(ROLE20) as roles
            from      menu_roles
            where     RLTYPE='58'
          ) t,
          TABLE(CAST(MULTISET(select LEVEL from dual connect by instr(t.roles, '|', 1, LEVEL - 1) > 0) as sys.odciNumberList)) lines
where     regexp_substr(t.roles,'[^|]+', 1, lines.column_value) is not null
order by  regexp_substr(t.roles,'[^|]+', 1, lines.column_value)

I'd understand that using PIVOT would be more efficient vs concatenating and splitting a string.

Thank you!

How about using UNION instead? select taskid, role1 from menu_roles where rltype='58' union select taskid, role2 from menu_roles where rltype='58' union ... — Erich Kitzmueller
– Erich Kitzmueller, Commented Apr 12, 2017 at 11:22
@Jaquio PL/SQL is Oracle's procedural language. Your code is entirely SQL (and not PL/SQL). — MT0
– MT0, Commented Apr 12, 2017 at 13:10

MT0 · Accepted Answer · 2017-04-12 13:15:05Z

1

You appear to want UNPIVOT:

SELECT task_id,
       role
FROM   menu_roles
UNPIVOT ( role FOR role_number IN ( ROLE1, ROLE2, ROLE3, ROLE4 /*, ... */ ) );

Or, using UNION ALL:

          SELECT task_id, role1 AS role FROM menu_roles
UNION ALL SELECT task_id, role2 AS role FROM menu_roles
UNION ALL SELECT task_id, role3 AS role FROM menu_roles
UNION ALL SELECT task_id, role4 AS role FROM menu_roles
-- ...

answered Apr 12, 2017 at 13:15

MT0

173k12 gold badges70 silver badges136 bronze badges

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

1 Comment

Jaquio Over a year ago

Thank you, @MT0. UNPIVOT was what I was looking for indeed

Collectives™ on Stack Overflow

Convert SQL query to pivot

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related