oracle行列互换总结___reference 小麦苗

 

 

1.概述

行列转换包括以下六种情况： 

1> 列转行

2> 行转列

3> 多列转换成字符串

4> 多行转换成字符串

5> 字符串转换成多列

6> 字符串转换成多行

 

下面分别进行举例介绍。

 

首先声明一点，有些例子需要如下10g 及以后才有的知识：

A. 掌握model子句

B. 正则表达式

C. 加强的层次查询

 

2.列转行

主要有union all、inset all into...select、model、collection方式  reference：小麦苗blog

2.1 union all ---主要方法

1> 列转行 例一：

----构建测试数据

CREATE  TABLE  t_col_row(

ID INT,

c1 VARCHAR2(10),

c2 VARCHAR2(10),

c3 VARCHAR2(10));

 

INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31');

INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL);

INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33');

INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34');

INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL);

INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35');

INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);

COMMIT;

 

SELECT * FROM t_col_row;

        ID C1         C2         C3

---------- ---------- ---------- ----------

         1 v11        v21        v31

         2 v12        v22

         3 v13                   v33

         4            v24        v34

         5 v15

         6                       v35

         7

7 rows selected.

 

----列转行

select id,'c1' cn,c1 cv from t_col_row

union all

select id,'c2' cn,c2 cv from t_col_row

union all

select id,'c3' cn,c3 cv from t_col_row;

        ID CN CV

---------- -- ----------

         1 c1 v11

         2 c1 v12

         3 c1 v13

         4 c1

         5 c1 v15

         6 c1

         7 c1

         1 c2 v21

         2 c2 v22

         3 c2

         4 c2 v24

         5 c2

         6 c2

         7 c2

         1 c3 v31

         2 c3

         3 c3 v33

         4 c3 v34

         5 c3

         6 c3 v35

         7 c3

21 rows selected.

 

----如果空行不需要转换，需要加where条件，如：

select id,'c1' cn,c1 cv from t_col_row where c1 is not null

union all

select id,'c2' cn,c2 cv from t_col_row where c2 is not null

union all

select id,'c3' cn,c3 cv from t_col_row where c3 is not null;

        ID CN CV

---------- -- ----------

         1 c1 v11

         2 c1 v12

         3 c1 v13

         5 c1 v15

         1 c2 v21

         2 c2 v22

         4 c2 v24

         1 c3 v31

         3 c3 v33

         4 c3 v34

         6 c3 v35

11 rows selected.

 

----如果要创建表

create table tablename as .......

 

2> 列转行 例二：

----构建测试数据

create table TEST_LHR

(

NAME VARCHAR2(255),

JANUARY NUMBER(18),

FEBRUARY NUMBER(18),

MARCH NUMBER(18),

APRIL NUMBER(18),

MAY NUMBER(18)

) ;

insert into TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

values ('长寿', 58, 12, 26, 18, 269);

insert into TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

values ('璧山', 33, 18, 17, 16, 206);

insert into TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

values ('杨家坪', 72, 73, 79, 386, 327);

insert into TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

values ('巫溪', 34, 9, 7, 21, 33);

insert into TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

values ('丰都', 62, 46, 39, 36, 91);

insert into TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

values ('武隆', 136, 86, 44, 52, 142);

commit;

 

sys@ORCL> select * from test_lhr;

NAME          JANUARY   FEBRUARY      MARCH      APRIL        MAY

---------- ---------- ---------- ---------- ---------- ----------

长寿               58         12         26         18        269

璧山               33         18         17         16        206

杨家坪             72         73         79        386        327

巫溪               34          9          7         21         33

丰都               62         46         39         36         91

武隆              136         86         44         52        142

6 rows selected.

 

----列转行

select * from (

select name, 'january' month,january v_num from test_lhr

union all

select name, 'february' month,february v_num from test_lhr

union all

select name, 'march' month,march v_num from test_lhr

union all

select name, 'april' month,april v_num from test_lhr

union all

select name, 'may' month,may v_num from test_lhr

)

order by name;

NAME       MONTH         V_NUM

---------- -------- ----------

长寿       may             269

长寿       april            18

长寿       february         12

长寿       march            26

长寿       january          58

丰都       may              91

丰都       march            39

丰都       april            36

丰都       february         46

丰都       january          62

巫溪       february          9

巫溪       may              33

巫溪       january          34

巫溪       april            21

巫溪       march             7

武隆       april            52

武隆       january         136

武隆       march            44

武隆       may             142

武隆       february         86

杨家坪     april           386

杨家坪     february         73

杨家坪     january          72

杨家坪     march            79

杨家坪     may             327

璧山       may             206

璧山       january          33

璧山       february         18

璧山       march            17

璧山       april            16

30 rows selected.

 

2.2 insert all into ...select

----首先需要创建一张表用于存放数据

create table test_zyx (name varchar2(10),month varchar2(10),v_num number(10));

 

insert all

into test_zyx(name,month,v_num) values(name,'may',may)

into test_zyx(name,month,v_num) values(name,'april',april)

into test_zyx(name,month,v_num) values(name,'february',february)

into test_zyx(name,month,v_num) values(name,'march',march)

into test_zyx(name,month,v_num) values(name,'january',january)

select t.name,t.january,t.february,t.march,t.april,t.may from test_lhr t;

 

30 rows created.

sys@ORCL>commit;

Commit complete.

 

sys@ORCL>select * from test_zyx;

NAME       MONTH           V_NUM

---------- ---------- ----------

长寿       may               269

丰都       may                91

巫溪       may                33

武隆       may               142

杨家坪     may               327

璧山       may               206

长寿       april              18

丰都       april              36

巫溪       april              21

武隆       april              52

杨家坪     april             386

璧山       april              16

长寿       february           12

丰都       february           46

巫溪       february            9

武隆       february           86

杨家坪     february           73

璧山       february           18

长寿       march              26

丰都       march              39

巫溪       march               7

武隆       march              44

杨家坪     march              79

璧山       march              17

长寿       january            58

丰都       january            62

巫溪       january            34

武隆       january           136

杨家坪     january            72

璧山       january            33

30 rows selected.

 

3.行转列

行转列可以利用MAX+DECODE、max+case..when..then、创建临时表、使用PL/SQL方式

----创建测试表，接上面实验

create table t_row_col as

select id,'c1' cn,c1 cv from t_col_row

union all

select id,'c2' cn,c2 cv from t_col_row

union all

select id,'c3' cn,c3 cv from t_col_row;

 

 

sys@ORCL>select * from t_row_col order by 1,2;

        ID CN CV

---------- -- ----------

         1 c1 v11

         1 c2 v21

         1 c3 v31

         2 c1 v12

         2 c2 v22

         2 c3

         3 c1 v13

         3 c2

         3 c3 v33

         4 c1

         4 c2 v24

         4 c3 v34

         5 c1 v15

         5 c2

         5 c3

         6 c1

         6 c2

         6 c3 v35

         7 c1

         7 c2

         7 c3

21 rows selected.

 

3.1 aggregate function(max+decode) ----主要方法

1> 行转列 例一：单列固定

select id,max(decode(cn,'c1',cv,null)) as c1,

       max(decode(cn,'c2',cv,null)) as c2,

       max(decode(cn,'c3',cv,null)) as c3

   from t_row_col

    group by id

    order by 1;

 

        ID C1         C2         C3

---------- ---------- ---------- ----------

         1 v11        v21        v31

         2 v12        v22

         3 v13                   v33

         4            v24        v34

         5 v15

         6                       v35

         7

7 rows selected.

 

注意：

max聚集函数也可以用sum、min、avg等其他聚集行数替代。

 

2> 行转列 例二：多列固定

 

sys@ORCL>select mgr,deptno,ename from scott.emp order by 1,2;

       MGR     DEPTNO ENAME

---------- ---------- ----------

      7566         20 SCOTT

      7566         20 FORD

      7698         30 JAMES

      7698         30 TURNER

      7698         30 WARD

      7698         30 ALLEN

      7698         30 MARTIN

      7782         10 MILLER

      7788         20 ADAMS

      7839         10 CLARK

      7839         20 JONES

      7839         30 BLAKE

      7902         20 SMITH

                   10 KING

14 rows selected.

 

 

select mgr,deptno,max(decode(empno,'7788',ename,null)) "7788",

               max(decode(empno,'7902',ename,null)) "7902",

               max(decode(empno,'7844',ename,null)) "7844",

               max(decode(empno,'7521',ename,null)) "7521",

               max(decode(empno,'7900',ename,null)) "7900",

               max(decode(empno,'7902',ename,null)) "7902"

   from scott.emp

     where mgr in (7566,7698)

      and deptno in (20,30)

     group by mgr,deptno

     order by 1,2;

 

MGR     DEPTNO 7788       7902       7844       7521       7900       7902

---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

7566         20 SCOTT      FORD                                        FORD

7698         30                       TURNER     WARD       JAMES

 

 

3> 固定列数的行转列

----准备数据

create table t4(id int,name varchar2(10),subject varchar2(20),grade number);

insert into t4 values(1,'ZORRO','语文',70);

insert into t4 values(2,'ZORRO','数学',80);

insert into t4 values(3,'ZORRO','英语',75);

insert into t4 values(4,'SEKER','语文',65);

insert into t4 values(5,'SEKER','数学',75);

insert into t4 values(6,'SEKER','英语',60);

insert into t4 values(7,'BLUES','语文',60);

insert into t4 values(8,'BLUES','数学',90);

insert into t4 values(9,'PG','数学',80);

insert into t4 values(10,'PG','英语',90);

commit;

 

 

sys@ORCL>select * from t4;

        ID NAME       SUBJECT                   GRADE

---------- ---------- -------------------- ----------

         1 ZORRO      语文                         70

         2 ZORRO      数学                         80

         3 ZORRO      英语                         75

         4 SEKER      语文                         65

         5 SEKER      数学                         75

         6 SEKER      英语                         60

         7 BLUES      语文                         60

         8 BLUES      数学                         90

         9 PG         数学                         80

        10 PG         英语                         90

10 rows selected.

 

select name,sum(case when SUBJECT='语文' then GRADE else 0 end) "语文",

          sum(case when SUBJECT='数学' then GRADE else 0 end) "数学",

          sum(case when SUBJECT='英语' then GRADE else 0 end) "英语"

       from t4 group by name;

 

NAME             语文       数学       英语

---------- ---------- ---------- ----------

SEKER              65         75         60

BLUES              60         90          0

PG                  0         80         90

ZORRO              70         80         75

 

select name,max(decode(SUBJECT,'语文',GRADE,0)) "语文" ,

          max(decode(SUBJECT,'数学',GRADE,0)) "数学" ,

          max(decode(SUBJECT,'英语',GRADE,0)) "英语"

    from t4  group by name;

 

 

----找出成绩中各科目都大于等于70)的学生

select name from t4 group by name having count(grade)=sum(case when grade>=70 then 1 else 0 end);

NAME

----------

PG

ZORRO

 

select name from t4 group by name having min(grade)>=70;

NAME

----------

PG

ZORRO

 

select * from (

      select name,max(decode(SUBJECT,'语文',GRADE,0)) "语文" ,

                max(decode(SUBJECT,'数学',GRADE,0)) "数学" ,

                max(decode(SUBJECT,'英语',GRADE,0)) "英语"

             from t4  group by name

            )

      where 语文>=70 and 数学>=70 and 英语>=70;

NAME             语文       数学       英语

---------- ---------- ---------- ----------

ZORRO              70         80         75

 

 

4> 不定列数行转列

如

c1 c2

--------------

1 我

1 是

1 谁

2 知

2 道

3 不

……

转换为

1 我是谁

2 知道

3 不

 

    这一类型的转换必须借助于PL/SQL来完成，这里给一个例子

create table test(c1 int,c2 varchar2(20));

insert into test values(1,'我')

insert into test values(1,'是');

insert into test values(1,'谁');

insert into test values(2,'知');

insert into test values(2,'道');

insert into test values(3,'不');

commit;

 

sys@ORCL>select * from test;

        C1 C2

---------- --------------------

         1 我

         1 是

         1 谁

         2 知

         2 道

         3 不

6 rows selected.

 

CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)

  RETURN VARCHAR2

  IS

  Col_c2 VARCHAR2(40);

BEGIN

  FOR cur IN (SELECT c2 FROM test WHERE c1=tmp_c1) LOOP

    Col_c2 := Col_c2||cur.c2;

  END LOOP;

  Col_c2 := rtrim(Col_c2,1);

  RETURN Col_c2;

END;

/

 

select distinct c1 ,get_c2(c1) cc2 from test;

        C1 CC2

---------- --------------------

         2 知道

         1 我是谁

         3 不 

 

还有一种行转列的方式，就是相同组中的行值变为单个列值，但转置的行值不变为列名：

ID CN_1 CV_1 CN_2 CV_2 CN_3 CV_3

1 c1 v11 c2 v21 c3 v31

2 c1 v12 c2 v22 c3

3 c1 v13 c2 c3 v33

4 c1 c2 v24 c3 v34

5 c1 v15 c2 c3

6 c1 c2 c3 v35

7 c1 c2 c3

 

这种情况可以用分析函数实现：

 

SELECT id, MAX(decode(rn, 1, cn, NULL)) cn_1,

         MAX(decode(rn, 1, cv, NULL)) cv_1,

         MAX(decode(rn, 2, cn, NULL)) cn_2,

         MAX(decode(rn, 2, cv, NULL)) cv_2,

         MAX(decode(rn, 3, cn, NULL)) cn_3,

         MAX(decode(rn, 3, cv, NULL)) cv_3

    FROM (SELECT id, cn, cv, row_number() over(PARTITION BY id ORDER BY cn, cv) rn

              FROM t_row_col)

     GROUP BY ID;

 

        ID CN CV_1       CN CV_2       CN CV_3

---------- -- ---------- -- ---------- -- ----------

         1 c1 v11        c2 v21        c3 v31

         2 c1 v12        c2 v22        c3

         3 c1 v13        c2            c3 v33

         4 c1            c2 v24        c3 v34

         5 c1 v15        c2            c3

         6 c1            c2            c3 v35

         7 c1            c2            c3

7 rows selected.

 

 

例二：接上面2.2 实验

SELECT t.name, MAX(decode(t.month, 'may', t.v_num)) AS may,

             MAX(decode(t.month, 'april', t.v_num)) AS april,

             MAX(decode(t.month, 'february', t.v_num)) AS february,

             MAX(decode(t.month, 'march', t.v_num)) AS march,

             MAX(decode(t.month, 'january', t.v_num)) AS january

    FROM test_zyx t

      GROUP BY t.name;

NAME              MAY      APRIL   FEBRUARY      MARCH    JANUARY

---------- ---------- ---------- ---------- ---------- ----------

长寿              269         18         12         26         58

巫溪               33         21          9          7         34

丰都               91         36         46         39         62

武隆              142         52         86         44        136

杨家坪            327        386         73         79         72

璧山              206         16         18         17         33

6 rows selected.

 

延伸：实现对不同区间的统计

SELECT  * FROM test_zyx  t  ORDER BY t.name,  t.month;

NAME       MONTH           V_NUM

---------- ---------- ----------

长寿       april              18

长寿       february           12

长寿       january            58

长寿       march              26

长寿       may               269

丰都       april              36

丰都       february           46

丰都       january            62

丰都       march              39

丰都       may                91

巫溪       april              21

巫溪       february            9

巫溪       january            34

巫溪       march               7

巫溪       may                33

武隆       april              52

武隆       february           86

武隆       january           136

武隆       march              44

武隆       may               142

杨家坪     april             386

杨家坪     february           73

杨家坪     january            72

杨家坪     march              79

杨家坪     may               327

璧山       april              16

璧山       february           18

璧山       january            33

璧山       march              17

璧山       may               206

30 rows selected.

 

SELECT t.name, CASE

WHEN t.v_num < 100 THEN

'0-100'

WHEN t.v_num >= 100 AND t.v_num < 200 THEN

'100-200'

WHEN t.v_num >= 200 AND t.v_num < 300 THEN

'200-300'

WHEN t.v_num >= 300 AND t.v_num < 400 THEN

'300-400'

END AS grade,

COUNT(t.v_num) count_num

FROM test_zyx t

GROUP BY t.name,

CASE

WHEN t.v_num < 100 THEN

'0-100'

WHEN t.v_num >= 100 AND t.v_num < 200 THEN

'100-200'

WHEN t.v_num >= 200 AND t.v_num < 300 THEN

'200-300'

WHEN t.v_num >= 300 AND t.v_num < 400 THEN

'300-400'

END;

 

NAME       GRADE    COUNT_NUM

---------- ------- ----------

长寿       200-300          1

杨家坪     300-400          2

武隆       0-100            3

巫溪       0-100            5

武隆       100-200          2

杨家坪     0-100            3

璧山       0-100            4

璧山       200-300          1

丰都       0-100            5

长寿       0-100            4

10 rows selected.

 

SELECT t2.grade,

MAX(decode(t2.name, '璧山', t2.count_num)) 璧山,

MAX(decode(t2.name, '长寿', t2.count_num)) 长寿,

MAX(decode(t2.name, '武隆', t2.count_num)) 武隆,

MAX(decode(t2.name, '丰都', t2.count_num)) 丰都,

MAX(decode(t2.name, '杨家坪', t2.count_num)) 杨家坪

FROM (SELECT t.name,

CASE

WHEN t.v_num < 100 THEN

'0-100'

WHEN t.v_num >= 100 AND t.v_num < 200 THEN

'100-200'

WHEN t.v_num >= 200 AND t.v_num < 300 THEN

'200-300'

WHEN t.v_num >= 300 AND t.v_num < 400 THEN

'300-400'

END AS grade,

COUNT(t.v_num) count_num

FROM test_zyx t

GROUP BY t.name,

CASE

WHEN t.v_num < 100 THEN

'0-100'

WHEN t.v_num >= 100 AND t.v_num < 200 THEN

'100-200'

WHEN t.v_num >= 200 AND t.v_num < 300 THEN

'200-300'

WHEN t.v_num >= 300 AND t.v_num < 400 THEN

'300-400'

END) t2

GROUP BY t2.grade;

 

GRADE         璧山       长寿       武隆       丰都     杨家坪

------- ---------- ---------- ---------- ---------- ----------

200-300          1          1

0-100            4          4          3          5          3

300-400                                                      2

100-200                                2

 

3.2 创建临时表的方式实现

----接上面3.行转列，创建的测试表

----基本的行转列可以通过创建临时表来实现，首先将基础表根据条件创建几个临时表，然后将这些临时表关联起来就可以了。

SELECT t1.id, t1.cv C1, t2.cv C2, t3.cv C3

   FROM (SELECT * FROM t_row_col t WHERE t.cn = 'c1') t1,

        (SELECT * FROM t_row_col t WHERE t.cn = 'c2') t2,

        (SELECT * FROM t_row_col t WHERE t.cn = 'c3') t3

     WHERE t1.id = t2.id

       AND t2.id = t3.id;

 

        ID C1         C2         C3

---------- ---------- ---------- ----------

         1 v11        v21        v31

         2 v12        v22

         3 v13                   v33

         4            v24        v34

         5 v15

         6                       v35

         7

7 rows selected.

 

4.多列转换成字符串

----用|| 或concat 函数可以实现：

sys@ORCL>SELECT concat('a',',') FROM dual;

CO

--

a,

 

sys@ORCL>SELECT * FROM t_col_row;

        ID C1         C2         C3

---------- ---------- ---------- ----------

         1 v11        v21        v31

         2 v12        v22

         3 v13                   v33

         4            v24        v34

         5 v15

         6                       v35

         7

7 rows selected.

sys@ORCL>select id,c1||','||c2||','||c3 as c123 from t_col_row;

        ID C123

---------- --------------------------------

         1 v11,v21,v31

         2 v12,v22,

         3 v13,,v33

         4 ,v24,v34

         5 v15,,

         6 ,,v35

         7 ,,

7 rows selected.

sys@ORCL>select concat(id,concat(c1,concat(c2,c3))) from t_col_row;

CONCAT(ID,CONCAT(C1,CONCAT(C2,C3)))

----------------------------------------------------------------------

1v11v21v31

2v12v22

3v13v33

4v24v34

5v15

6v35

7

7 rows selected.

 

5.多行转换成字符串

 

5.1 环境准备

CREATE TABLE t_row_str(

ID INT,

col VARCHAR2(10)

);

 

INSERT INTO t_row_str VALUES(1,'a');

INSERT INTO t_row_str VALUES(1,'b');

INSERT INTO t_row_str VALUES(1,'c');

INSERT INTO t_row_str VALUES(2,'a');

INSERT INTO t_row_str VALUES(2,'d');

INSERT INTO t_row_str VALUES(2,'e');

INSERT INTO t_row_str VALUES(3,'c');

COMMIT;

 

sys@ORCL>select * from t_row_str;

        ID COL

---------- ----------

         1 a

         1 b

         1 c

         2 a

         2 d

         2 e

         3 c

7 rows selected.

 

5.2 MAX+DECODE

select id,max(decode(rn,1,col,null))||max(decode(rn,2,','||col,null))||

       max(decode(rn,3,','||col,null)) str

   from (select id,col,row_number() over(partition by id order by col) as rn

            from t_row_str) t

      group by id order by 1;

 

        ID STR

---------- --------------------------------

         1 a,b,c

         2 a,d,e

         3 c

 

 

 

 

 

 

 

reference    http://blog.itpub.net/26736162/viewspace-1272538/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2122257/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2122257/