How do I turn rows with duplicate values to columns?

Question

I try to pivot my table but keeping additional rows (in my example eeeeee ) Is there a way in Oracle SQL to do this?

select * from (
select 
    mat_table.material, attribute_table.attribute, attribute_table.value
  from 
    mat_table mat_table
    inner join 
    attribute_table on mat_table.rel= attribute_table.rel
    where 




      material = 'Material_A' 
            )


material    |attribute|  value
_____________________________________

Material_A  |aaaaaa    | 
Material_A  |bbbbbb    |       hello
Material_A  |cccccc    |       val_1
Material_A  |dddddd    |       2
Material_A  |eeeeee    |       15
Material_A  |eeeeee    |       16
Material_A  |eeeeee    |       24 

when I use pivot under the where clause

    pivot (
    max(attribute) as max_value for attribute IN ( 'aaaaaa', 
                                                   'bbbbbb', 
                                                   'cccccc', 
                                                   'dddddd', 
                                                   'eeeeee'
                                      ))

I am getting closer to what I want but for eeeee I get only one value

material    |aaaaaa | bbbbbb | cccccc | dddddd | eeeeee |
__________________________________________________________
Material_A  |       | hello  | val_1  | 2      | 24     |

but what I want is something like

material    |aaaaaa | bbbbbb | cccccc | dddddd | eeeeee_1 | eeeeee_2 | eeeeee_3 |
   __________________________________________________________________________________
Material_A  |       | hello  | val_1  | 2      | 15            16    |     24

Answer 1

If there are always 3 values for eeeeee then you can do it as following

SQL> with mat_table (material, attribute, value) as
  2  (
  3  select 'Material_A', 'aaaaaa', null from dual
  4  union all select 'Material_A', 'bbbbbb', 'hello' from dual
  5  union all select 'Material_A', 'cccccc', 'val_1' from dual
  6  union all select 'Material_A', 'dddddd', '2' from dual
  7  union all select 'Material_A', 'eeeeee', '15' from dual
  8  union all select 'Material_A', 'eeeeee', '16' from dual
  9  union all select 'Material_A', 'eeeeee', '24' from dual
 10  )
 11  select *
 12    from (select t.*,
 13                 row_number() over(partition by attribute order by value) rn
 14            from mat_table t)
 15  pivot (max(value) for (attribute, rn) in
 16  (
 17   ('aaaaaa', 1), ('bbbbbb', 1), ('cccccc', 1), ('dddddd', 1),
 18   ('eeeeee', 1), ('eeeeee', 2), ('eeeeee', 3)
 19  ));

MATERIAL   'aaaa 'bbbb 'cccc 'dddd 'eeee 'eeee 'eeee
---------- ----- ----- ----- ----- ----- ----- -----
Material_A       hello val_1 2     15    16    24

If, however, you expect Oracle to dynamically create columns for any number of values for eeeeee then that is not possible.

Please read detailed explanation here Oracle Dynamic Pivoting

You can generate XML for any combinations of attribute and value but if you want to display result using SQL then eventually all the columns must be specified (alternative approach is parsing XML on client side).

SQL> with mat_table (material, attribute, value) as
  2  (
  3  select 'Material_A', 'aaaaaa', null from dual
  4  union all select 'Material_A', 'bbbbbb', 'hello' from dual
  5  union all select 'Material_A', 'cccccc', 'val_1' from dual
  6  union all select 'Material_A', 'dddddd', '2' from dual
  7  union all select 'Material_A', 'eeeeee', '15' from dual
  8  union all select 'Material_A', 'eeeeee', '16' from dual
  9  union all select 'Material_A', 'eeeeee', '24' from dual
 10  )
 11  select material, x.*
 12  from mat_table
 13  pivot xml (count(*) as dummy for (attribute, value) in (any, any))
 14  -- parsing output
 15  , xmltable('/PivotSet' passing attribute_value_xml
 16             columns
 17               aaaaaa varchar2(10) path '/PivotSet/item[column="aaaaaa"]/column[2]',
 18               bbbbbb varchar2(10) path '/PivotSet/item[column="bbbbbb"]/column[2]',
 19               cccccc varchar2(10) path '/PivotSet/item[column="cccccc"]/column[2]',
 20               dddddd varchar2(10) path '/PivotSet/item[column="dddddd"]/column[2]',
 21               eeeeee_1 varchar2(10) path '/PivotSet/item[column="eeeeee"][1]/column[2]',
 22               eeeeee_2 varchar2(10) path '/PivotSet/item[column="eeeeee"][2]/column[2]',
 23               eeeeee_3 varchar2(10) path '/PivotSet/item[column="eeeeee"][3]/column[2]') x;

MATERIAL   AAAAAA     BBBBBB     CCCCCC     DDDDDD     EEEEEE_1   EEEEEE_2   EEEEEE_3
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Material_A            hello      val_1      2          15         16         24

In this case there is no guarantee that EEEEEE_1/EEEEEE_2/EEEEEE_3 will be 15/16/24 in exactly this order.