Oracle 行列转换总结

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

*列转行

*行转列

*多列转换成字符串

*多行转换成字符串

*字符串转换成多列

*字符串转换成多行

下面分别进行举例介绍。

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

a、掌握model子句

b、正则表达式

c、加强的层次查询

讨论的适用范围只包括8i,9i,10g及以后版本。begin：

1、列转行

未列转行之前的效果如下：

列转行的效果如下：

sql代码:

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,‘v24‘,‘v34‘);
INSERT INTO t_col_row VALUES (5,‘v15‘,NULL);
INSERT INTO t_col_row VALUES (6,‘v35‘);
INSERT INTO t_col_row VALUES (7,NULL);
COMMIT;
 
SELECT * FROM t_col_row;

1).UNION ALL–>适用范围：8i,10g及以后版本
sql代码：

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;

若空行不需要转换，只需加一个where条件，

sql代码：

WHERE COLUMN IS NOT NULL  

?

2).MODEL–>适用范围：10g及以后

SELECT id,cn,cv FROM t_col_row
MODEL
RETURN UPDATED ROWS
PARTITION BY (ID)
DIMENSION BY (0 AS n)
MEASURES (‘xx‘ AS cn,‘yyy‘ AS cv,c1,c2,c3) --xx、yyy表示字段长度
RULES UPSERT ALL
(
  cn[1] = ‘c1‘,cn[2] = ‘c2‘,cn[3] = ‘c3‘,cv[1] = c1[0],cv[2] = c2[0],cv[3] = c3[0]
)
ORDER BY ID,cn;

?

?现在小分析一下上面这个查询：

partition by(prd_type_id)指定结果是根据prd_type_id分区的。

dimension by(0 as n) 定义数组的长度，这就意味着必须提供数组索引才能访问数组中的单元。

measures(‘xx‘ AS cn)表明数组中的每个单元包含一个数量，同时表明数组名为cn。

3).collection->适用范围：8i,10g及以后版本

要创建一个对象和一个集合：

sql语句：

create TYPE cn_pair as OBJECT(cn VARCHAR(10),cv VARCHAR2(10));
CREATE TYPE cv_varr AS VARAY(8) OF cv_pair;

select id,t.cn AS cn,t.cv AS cv
  FROM t_col_row,TABLE(cv_varr(cv_pair(‘c1‘,t_col_row.c1),cv_pair(‘c2‘,t_col_row.c2),cv_pair(‘c3‘,t_col_row.c3)
   
))  t
ORDER BY 1,2

2、行转列

未行转列之前的效果如下：

行转列效果如下：

CREATE TABLE t_row_col AS
SELECT id,c1 cv
  FROM t_col_row
UNION ALL
SELECT id,c2 cv
  FROM t_col_row
UNION ALL
SELECT id,c3 cv FROM t_col_row;
 
SELECT * FROM t_row_col ORDER BY 1,2;

1）AGGREGATE ?FUNCTION ->适用范围：8i,10g及以后版本

select id,max(decode(cn,‘c1‘,cv,null)) as cl,‘c2‘,null)) as c2,‘c3‘,null))  as c3
from t_row_col
group by id
order by 1

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

被指定的转置列只能有一列，但固定的列可以有多列，请看下面的例子：

select mgr,depton,ename from scott.emp order by 1,2;

select mgr,deptno,max(decode(empno,‘7788‘,ename,null)) "7788",‘7902‘,null)) "7902",MAX(decode(empno,‘7844‘,NULL)) "7844",‘7521‘,NULL)) "7521",‘7900‘,NULL)) "7900",‘7499‘,NULL)) "7499",‘7654‘,NULL)) "7654"
from scott.emp
where mgr in (7566,7698)
          and deptno in (20,30)
group by mgr,deptno
order by 1,2
           
           

这里转置列为empno,固定列为mgr,deptno。

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

?

?

?

参考来源：http://www.cnblogs.com/linjiqin/p/3148808.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!