Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I turn rows with duplicate values to columns?

Tags:

sql

oracle

pivot

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
like image 513
user6490055 Avatar asked Oct 17 '22 10:10

user6490055


1 Answers

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.

like image 173
Dr Y Wit Avatar answered Oct 20 '22 23:10

Dr Y Wit