Oracle 11g: Unpivot multiple columns and include column name

Change your UNPIVOT to be like this

select *
from (
  select IDNUM,F1,F2,F3,E1,E2,H1,H2,
    null as E3,null as H3
  from T5
) 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

This should do the trick


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

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


If you need to store result of UNPIVOT you could 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         │
└───────┴────┴─────────┴─────────┴─────────┴────────────┘