Multiply the number of columns by the number of rows

user_5968681 0
Viewed 66

I have a table like this:

  +------+--------+--------+--------+
  | TYPE | PROP_X | PROP_Y | PROP_Z |
  +------+--------+--------+--------+
  |    1 | x1     | y1     | z1     |
  |    2 | x2     | y2     | z2     |
  |    3 | x3     | y3     | z3     |
  |    4 | x4     | y4     | z4     |
  +------+--------+--------+--------+

How can I get such result:

+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| PROP_X_1 | PROP_X_2 | PROP_X_3 | PROP_X_4 | PROP_Y_1 | PROP_Y_2 | PROP_Y_3 | PROP_Y_4 | PROP_Z_1 | PROP_Z_2 | PROP_Z_3 | PROP_Z_4 |
+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| x1       | x2       | x3       | x4       | y1       | y2       | y3       | y4       | z1       | z2       | z3       | z4       |
+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+

I guess, operator PIVOT can be used for this, but I can't understand how.

P.S. Create script for the table:

create table my_table(type number(1), prop_x varchar2(30 char), prop_y varchar2(30 char), prop_z varchar2(30 char));

insert into my_table select 1 as type, 'x1' as prop_x, 'y1' as prop_y, 'z1' as prop_z from dual union all select 2, 'x2', 'y2', 'z2' from dual union all select 3, 'x3', 'y3', 'z3' from dual union all select 4, 'x4', 'y4', 'z4' from dual; 
commit;

UPD: Primarily, I need solution at least for case when type only in (1,2,3,4)

pivot oracle sql
2 Answers

You can use conditional aggregation:

select max(case when type = 1 then prop_x end) as prop_x_1,
       max(case when type = 2 then prop_x end) as prop_x_2,
       max(case when type = 3 then prop_x end) as prop_x_3,
       max(case when type = 4 then prop_x end) as prop_x_4,
       max(case when type = 1 then prop_y end) as prop_y_1,
       . . .
from my_table;
user_11440350

Solution with pivot - working only with restricted number of rows (and 1_prop_x instead of prop_x_1 plus columns order is different from the question but it is ok for me):

  select * 
  from 
  (
  select *
  from my_table t
  )
  pivot(
  max(prop_x) as prop_x, max(prop_y) as prop_y, max(prop_z) as prop_z
  for type in (1,2,3,4)
  )
user_59686810
Related