Oracle SQL transform more row in one row

Question

Create table EMP_TEST(
    ID number,
    emp number,
    type number,
    C_1 number,
    C_2 number,
    C_3 number,
    C_4 number
)


Insert into EMP_TEST (ID,emp,type,C_1,C_2,C_3,C_4) VALUES (1,100,10,8,8,null,null);
Insert into EMP_TEST (ID,emp,type,C_1,C_2,C_3,C_4) VALUES (2,100,20,null,null,8,8);
Insert into EMP_TEST (ID,emp,type,C_1,C_2,C_3,C_4) VALUES (3,200,10,7,7,null,null);
Insert into EMP_TEST (ID,emp,type,C_1,C_2,C_3,C_4) VALUES (4,200,20,null,null,7,7);

Is it possible get resault as:

EMP  TYPE_10  C_1  TYPE_10  C_2  TYPE_20  C_3  TYPE_20  C_4
100  10       8    10       8    20       8    20       8
200  10       7    10       7    20       7    20       7

I one row I need display one emp where C_1,C_2,C_3,C_4 is not null!

Littlefoot · Accepted Answer · 2021-03-24 13:42:38Z

2

Is it possible? Yes, if that's what you're looking for:

SQL> select emp,
  2    max(case when type = 10 then type end) as type_10,
  3    max(c_1) as c_1,
  4    max(case when type = 10 then type end) as type_10,
  5    max(c_2) as c_2,
  6    --
  7    max(case when type = 20 then type end) as type_20,
  8    max(c_3) as c_3,
  9    max(case when type = 20 then type end) as type_20,
 10    max(c_4) as c_4
 11  from emp_test
 12  group by emp;

       EMP    TYPE_10        C_1    TYPE_10        C_2    TYPE_20        C_3    TYPE_20        C_4
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       100         10          8         10          8         20          8         20          8
       200         10          7         10          7         20          7         20          7

SQL>

answered Mar 24, 2021 at 13:42

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL transform more row in one row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related