2

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)

archjkee
  • 41
  • 4
  • 1
    You can only use `PIVOT` for this, if you restrict the query to certain rows, e.g. type 1, 2, 3, and 4. The reason for this is that an SQL query has always fixed columns. If you want to have it working for a variable number of columns (i.e. whatever rows you have in your table), then you need dynamic SQL. That is basically: select all types, then create a new query based on the types found. What you would usually do, however, is select all data unformatted and then put them in a grid/table in your GUI (app or Website). – Thorsten Kettner Jan 15 '19 at 14:55

2 Answers2

0

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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)
  )
archjkee
  • 41
  • 4