UNPIVOT 在多列上返回多列

2021-09-14 00:00:00 sql oracle unpivot

以下是PL/SQL中要实现的要求-

Below is the requirement to be acheived in PL/SQL-

表格格式为

CREATE TABLE NETWORK_TABLE ( ORIG_CODE NUMBER, ORIG_SV NUMBER, DEST_CODE NUMBER, DEST_SV NUMBER )

样本数据 - 

INSERT INTO network_table VALUES ( 14, 1, 15, 1);

INSERT INTO network_table VALUES ( 18, 4, 11, 1);

INSERT INTO network_table VALUES ( 15, 1, 22, 3);

INSERT INTO network_table VALUES ( 23, 2, 21, 1);

INSERT INTO network_table VALUES ( 14, 3, 11, 1);

INSERT INTO network_table VALUES ( 12, 2, 22, 2);

表格数据看起来像 -

Table data looks like - 

Orig_r  orig_sv  dest_r dest_sv
  14     1       15     1 
  12     2       22     2 
  18     4       11     1 
  15     1       22     3 
  14     3       11     1

现在，我想得到如下输出 -

Now, I want to get the output as below - 

ROOT SV
14   1 
15   1  
12   2
22   2
18   4
11   1
15   1
22   3
14   3
1    1

我怎样才能做到这一点?感谢您的意见

How can I acheive this? Appreciate your input

推荐答案

像这样:

SQL> select * from network_table;

 ORIG_CODE    ORIG_SV  DEST_CODE    DEST_SV
---------- ---------- ---------- ----------
        14          1         15          1
        12          2         22          2
        18          4         11          1
        15          1         22          3
        14          3         11          1

SQL> select case name when 'ORIG_SV' then orig_code else dest_code end code, val 
  2  from network_table 
  3  unpivot (val for name in (orig_sv, dest_sv));

      CODE        VAL
---------- ----------
        14          1
        15          1
        12          2
        22          2
        18          4
        11          1
        15          1
        22          3
        14          3
        11          1

或 10g 及以下:

SQL> select case  r when 1 then orig_code else dest_code end code,
  2         case r when 1 then orig_sv else dest_sv end val
  3    from network_table, (select rownum r from dual connect by level <= 2)
  4  /

      CODE        VAL
---------- ----------
        14          1
        12          2
        18          4
        15          1
        14          3
        15          1
        22          2
        11          1
        22          3
        11          1

相关文章