SQL Puzzle - Transpose Rows and Shift Values among columns

This SQL Puzzle is inspired from the problem which I solved recently, and I think the problem is quite interesting to be shared as a SQL Puzzle. It includes shifting the values among columns and also transpose of rows. I hope you will enjoy solving the problem.

We have an existing table and data with following rules -
1) We have a table of hierarchy going upto 4 levels for different PRODUCTS and Hierarchy TYPE.
2) We have only 3 rows per Product
3) Each hierarchy can be of PRODUCTION / SALE / SUPPORT type
4) Each Hierarchy may have GM -> RM -> MGR -> EXEC roles
5) In some cases any of the role from Hierarchy can be missing, like it can be GM -> MRG -> EXEC or GM -> MGR.

Following is the script for sample table and data:

create table product_staff_hierarchy
(
  product varchar2(100),
  level1_ecode number(10),
  level1_ename varchar2(100),
  level1_erole varchar2(100),
  level2_ecode number(10),
  level2_ename varchar2(100),
  level2_erole varchar2(100),
  level3_ecode number(10),
  level3_ename varchar2(100),
  level3_erole varchar2(100),
  level4_ecode number(10),
  level4_ename varchar2(100),
  level4_erole varchar2(100),
  hierarchy_type varchar2(100)
);

insert into product_staff_hierarchy
select 'MOBILE',  1,  'M1',  'GM',  2,  'M2',  'RM' ,  3,    'M3',   'MGR',  4,   'M4',   'EXEC', 'PRODUCTION' FROM DUAL UNION ALL
select 'MOBILE',  5,  'M5',  'GM',  6,  'M6',  'MGR',  7,    'M7',   'EXEC', NULL, NULL,  NULL,   'SALE'       FROM DUAL UNION ALL
select 'MOBILE',  8,  'M8',  'GM',  9,  'M9',  'RM',   10,   'M10',  'EXEC', NULL, NULL,  NULL,   'SUPPORT'    FROM DUAL UNION ALL
select 'LAPTOP',  11, 'L11', 'RM',  12, 'L12', 'EXEC', NULL, NULL,   NULL,   NULL, NULL,  NULL,   'PRODUCTION' FROM DUAL UNION ALL
select 'LAPTOP',  13, 'L13', 'GM',  14, 'L14', 'MGR',  15,   'L15',  'EXEC', NULL, NULL,  NULL,   'SALE'       FROM DUAL UNION ALL
select 'LAPTOP',  16, 'L16', 'GM',  17, 'L17', 'MGR',  NULL, NULL,   NULL,   NULL, NULL,  NULL,   'SUPPORT'    FROM DUAL UNION ALL
select 'SPEAKER', 18, 'S18', 'GM',  19, 'S19', 'RM',   20,   'S20',  'MGR',  21,   'L21', 'EXEC', 'SALE'       FROM DUAL UNION ALL
select 'SPEAKER', 22, 'S22', 'MGR', 23, 'S23', 'EXEC', NULL, NULL,   NULL,   NULL, NULL,  NULL,   'PRODUCTION' FROM DUAL UNION ALL
select 'SPEAKER', 24, 'S24', 'GM',  25, 'S25', 'EXEC', NULL, NULL,   NULL,   NULL, NULL,  NULL,   'SUPPORT'    FROM DUAL UNION ALL
select 'MOUSE',  26, 'M26', 'GM',  27, 'M27', 'MGR',  28,   'M28', 'EXEC',  NULL, NULL,  NULL,   'PRODUCTION' FROM DUAL UNION ALL
select 'MOUSE',  29, 'M29', 'RM',  30, 'M30', 'MGR',  31,   'M31', 'EXEC',  NULL, NULL,  NULL,   'SALE'       FROM DUAL UNION ALL
select 'MOUSE',  32, 'M32', 'GM',  33, 'M33', 'RM',   34,   'M34', 'EXEC',  NULL, NULL,  NULL,   'SUPPORT'    FROM DUAL UNION ALL
select 'KEYBOARD',  35, 'K35', 'GM',  36, 'K36', 'RM',   37,   'K27', 'MGR',   NULL, NULL,  NULL,   'PRODUCTION' FROM DUAL UNION ALL
select 'KEYBOARD',  38, 'K38', 'MGR', 39, 'K29', 'EXEC', NULL, NULL,   NULL,   NULL, NULL,  NULL,   'SUPPORT'    FROM DUAL UNION ALL
select 'CHARGER', 40, 'C40', 'RM',  41, 'C41', 'EXEC', NULL, NULL,   NULL,   NULL, NULL,  NULL,   'PRODUCTION' FROM DUAL UNION ALL
select 'CHARGER', 42, 'C42', 'GM',  43, 'C43', 'RM',   44,   'C44', 'MGR',   NULL, NULL,  NULL,   'SALE'       FROM DUAL;

Now here is the Problem Statement:
We want to transpose above data in the following format
1) Per Product, we would have only one row.
2) We will have ROLE_TYPE column for each combination like SALE_EXEC or SUPPORT_RM
3) Values related to product and role combination should go in their designated columns
4) Multiple hierarchies of each type should be transposed into one row per product
5) The structure of final output should be as per the following table structure

create table product_staff_new
(
  product varchar2(100),
  production_gm_ecode   number(10),
  production_gm_ename   varchar2(100),
  production_rm_ecode   number(10),
  production_rm_ename   varchar2(100),
  production_mgr_ecode  number(10),
  production_mgr_ename  varchar2(100),
  production_exec_ecode number(10),
  production_exec_ename varchar2(100),
  sale_gm_ecode         number(10),
  sale_gm_ename         varchar2(100),
  sale_rm_ecode         number(10),
  sale_rm_ename         varchar2(100),
  sale_mgr_ecode        number(10),
  sale_mgr_ename        varchar2(100),
  sale_exec_ecode       number(10),
  sale_exec_ename       varchar2(100),
  support_gm_ecode      number(10),
  support_gm_ename      varchar2(100),
  support_rm_ecode      number(10),
  support_rm_ename      varchar2(100),
  support_mgr_ecode     number(10),
  support_mgr_ename     varchar2(100),
  support_exec_ecode    number(10),
  support_exec_ename    varchar2(100)
);

The original problem was having 8 Products, 10 Levels, and 8 columns per level, and was originally solved with PL/SQL - loops and dynamic SQL. When this requirement came to me, I was able to solve it with only one SQL and gained a-lot of performance.

Do share your approach in the comment box. Have fun writing the SQL or PL/SQL, Enjoy :)

More SQL Puzzles:
- Graph Shortest Path Solution by SQL
- Diamond Shaped Star Pattern by SQL
- Sorting Versions stored in Varchar2 Column
- SQL Puzzle - Grouping Deals
- SQL Puzzle - Consecutive Wins
- SQL Puzzle - Issue Tracker
- SQL Interview Question Answers