oracle11g - can someone please clarify my doubts on pivot table in oracle -

in input table, key , value columns names. below input table , want below table in o/p format data not coming ... when can % symbol in square brackets out using pivot in query data displaying.but same i'm using in pivot displaying blanks. me how use % symbol , operator in pivot query in advance.

input table:

key      value  ------------------- sal[0]    2000  sal[1]    3000  sal[2]    1000  emp[0]    10  emp[1]    20  emp[2]    30  ename[0]  rag  ename[1]  swa  ename[2]  yag 

output format:

  emp   ename sal   ----------------    10    rag   2000    20    swa   3000    30    yag   1000  

my query is:

 select *     input   pivot (     max (value) key in ('emp[%]' emp,                             'ename[%]' ename,                             'sal[%]' sal                             )  ); 

values listed in in clause of pivot clause expected constant values of pivot column. while percent sign in like condition considered wildcard, in inclause of pivot clause literal.

as 1 of options, can rid of square brackets(unless carrying special value) using regexp_substr() regular expression function , pivot:

sql> t1(key1, value1) as(   2    select 'sal[0]'  ,  '2000'  dual union   3    select 'sal[1]'  ,  '3000'  dual union   4    select 'sal[2]'  ,  '1000'  dual union   5    select 'emp[0]'  ,  '10'    dual union   6    select 'emp[1]'  ,  '20'    dual union   7    select 'emp[2]'  ,  '30'    dual union   8    select 'ename[0]',  'rag'   dual union   9    select 'ename[1]',  'swa'   dual union  10    select 'ename[2]',  'yag'   dual  11  )  12  select emp  13       , sal  14       , ename  15    ( select regexp_substr(key1, '^[[:alpha:]]+') key1  16                , value1  17                , row_number() over(partition regexp_substr(key1, '^[[:alpha:]]+') order value1)  18             t1  19          )  20  pivot(  21    max(value1)  key1 in ('emp' emp, 'sal' sal, 'ename' ename)  22  )  23  ; 

result:

emp  sal   ename ---- ----  ----- 10   1000  rag 20   2000  swa 30   3000  yag