Oracle 11g: Unpivot multiple columns and column name

Question

I am trying to expand multiple columns in my dataset. This is what my data looks like.

CREATE TABLE T5 (idnum NUMBER,f1 NUMBER(10,5),f2 NUMBER(10,5),f3 NUMBER(10,5) ,e1 NUMBER(10,5),e2 NUMBER(10,5) ,h1 NUMBER(10,5),h2 NUMBER(10,5)); INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (1,'10.2004','5.009','7.330','9.008','8.003','.99383','1.43243'); INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2 VALUES (2,'4.2004','6.009','9.330','4.7008','4.60333','1.993','3.3243'); INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (3,'10.2040','52.6009','67.330','9.5008','8.003','.99383','1.43243'); INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (4,'9.20704','45.009','17.330','29.008','5.003','3.9583','1.243'); COMMIT; select * from t5; IDNUM F1 F2 F3 E1 E2 H1 H2 1 10.2004 5.009 7.33 9.008 8.003 0.99383 1.43243 2 4.2004 6.009 9.33 4.7008 4.60333 1.993 3.3243 3 10.204 52.6009 67.33 9.5008 8.003 0.99383 1.43243 4 9.20704 45.009 17.33 29.008 5.003 3.9583 1.243 

I do not like this...

 select * from (select IDNUM,F1,F2,F3,E1,E2,H1,H2, null as E3,null as H3 from T5) UnPivot((F,E,H) for sk in ((F1,E1,H1) as 1, (F2,E2,H2) as 2, (F3,E3,H3) as 3)) order by IDNUM,SK; IDNUM SK FEH ----- -- ------- ------- ------- 1 1 10.2004 9.008 .99383 1 2 5.009 8.003 1.43243 1 3 7.33 null null 2 1 4.2004 4.7008 1.993 2 2 6.009 4.60333 3.3243 2 3 9.33 null null 3 1 10.204 9.5008 .99383 3 2 52.6009 8.003 1.43243 3 3 67.33 null null 4 1 9.20704 29.008 3.9583 4 2 45.009 5.003 1.243 4 3 17.33 null null 

But I really need the following:

 IDNUM SK FEH F_COL_NAME ----- -- ------- ------- ------- ---------- 1 1 10.2004 9.008 .99383 F1 1 2 5.009 8.003 1.43243 F2 1 3 7.33 null null F3 2 1 4.2004 4.7008 1.993 F1 2 2 6.009 4.60333 3.3243 F2 2 3 9.33 null null F3 3 1 10.204 9.5008 .99383 F1 3 2 52.6009 8.003 1.43243 F2 3 3 67.33 null null F3 4 1 9.20704 29.008 3.9583 F1 4 2 45.009 5.003 1.243 F2 4 3 17.33 null null F3 

How can i do this?

Answer 1

Just select idnum, sk, f, e, h, 'F'||SK as col_name ... You need to specify all columns instead of an asterisk.

Like http://sqlfiddle.com/#!4/12446/21

Answer 2

Change your UNPIVOT to be like that

 select A.*, sk from (select IDNUM,F1,F2,F3,E1,E2,H1,H2, null as E3,null as H3 from T5) AS A UnPivot((F,E,H) for sk in ((F1,E1,H1) as 'F1', (F2,E2,H2) as 'F2', (F3,E3,H3) as 'F3')) order by IDNUM,SK; 

It should do its thing

Answer 3

If you need to save the result of UNPIVOT , you can use INSERT ALL :

 CREATE TABLE T5_unpiv(IDNUM NUMBER,SK NUMBER,F NUMBER,E NUMBER,H NUMBER ,F_COL_NAME VARCHAR2(100)); INSERT ALL INTO T5_unpiv(IDNUM,SK,F,E,H,F_COL_NAME) VALUES(idnum,1,f1,e1,h1,'F1') INTO T5_unpiv(IDNUM,SK,F,E,H,F_COL_NAME) VALUES(idnum,2,f2,e2,h2,'F2') INTO T5_unpiv(IDNUM,SK,F,E,H,F_COL_NAME) VALUES(idnum,3,f3,NULL,NULL,'F3') SELECT * FROM T5; SELECT * FROM T5_unpiv; 

DBFiddle Demo

Output:

 ┌───────┬────┬─────────┬─────────┬─────────┬────────────┐ │ IDNUM │ SK │ F │ E │ H │ F_COL_NAME │ ├───────┼────┼─────────┼─────────┼─────────┼────────────┤ │ 1 │ 1 │ 10.2004 │ 9.008 │ .99383 │ F1 │ │ 1 │ 2 │ 5.009 │ 8.003 │ 1.43243 │ F2 │ │ 1 │ 3 │ 7.33 │ null │ null │ F3 │ │ 2 │ 1 │ 4.2004 │ 4.7008 │ 1.993 │ F1 │ │ 2 │ 2 │ 6.009 │ 4.60333 │ 3.3243 │ F2 │ │ 2 │ 3 │ 9.33 │ null │ null │ F3 │ │ 3 │ 1 │ 10.204 │ 9.5008 │ .99383 │ F1 │ │ 3 │ 2 │ 52.6009 │ 8.003 │ 1.43243 │ F2 │ │ 3 │ 3 │ 67.33 │ null │ null │ F3 │ │ 4 │ 1 │ 9.20704 │ 29.008 │ 3.9583 │ F1 │ │ 4 │ 2 │ 45.009 │ 5.003 │ 1.243 │ F2 │ │ 4 │ 3 │ 17.33 │ null │ null │ F3 │ └───────┴────┴─────────┴─────────┴─────────┴────────────┘ 

Answer 4

Try it.

 select * from (select IDNUM,F1,F2,F3,E1,E2,H1,H2, null as E3,null as H3 from T5) UnPivot((F,E,H) for sk in ((F1,E1,H1) as 'F1', (F2,E2,H2) as 'F2', (F3,E3,H3) as 'F3')) order by IDNUM,SK;