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, 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;

1).UNION ALL–>适用范围：8i,9i,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,9i,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' 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;
 
SELECT * FROM t_row_col ORDER BY 1,2;

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

select id ,
           max(decode(cn,'c1',cv,null)) as cl,
           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

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",
          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, '7499', ename, NULL)) "7499",
         MAX(decode(empno, '7654', ename, 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