Oracle 11g:逆透视多个列并包含列名 [英] Oracle 11g: Unpivot multiple columns and include column name

问题描述

我正在尝试对数据集中的多个列进行反透视.这是我的数据的样子.

I'm triyng to unpivot multiple columns in my dataset. Here's what my data look 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'm unpivoting like so...

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        F        E        H
-----  --  -------  -------  -------
    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 what I really need is as follows...

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

我该怎么做?

推荐答案

只需 select idnum, sk, f, e, h, 'F'||SK as col_name ... 你需要指定所有列而不是星号.

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

像这样http://sqlfiddle.com/#!4/12446/21

这篇关于Oracle 11g:逆透视多个列并包含列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！