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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With