行列转换包括以下六种情况:
*列转行
*行转列
*多列转换成字符串
*多行转换成字符串
*字符串转换成多列
*字符串转换成多行
下面分别进行举例介绍。
首先声明一点,有些例子需要如下10g及以后才有的知识:
a、掌握model子句
b、正则表达式
c、加强的层次查询
讨论的适用范围只包括8i,9i,10g及以后版本。begin:
1、列转行
未列转行之前的效果如下:
列转行的效果如下:
sql代码:
CREATE TABLEt_col_row(
IDINT,
c1VARCHAR2(10),
c2VARCHAR2(10),
c3VARCHAR2(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 cvFROMt_col_rowUNION ALL
SELECT id, 'c2'cn, c2 cvFROMt_col_rowUNION 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 FROMt_col_row
MODELRETURNUPDATED ROWS
PARTITIONBY(ID)
DIMENSIONBY (0 ASn)
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) OFcv_pair;select id,t.cn AS cn,t.cv AScvFROM 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)
)) tORDER BY 1,2
2、行转列
未行转列之前的效果如下:
行转列效果如下:
CREATE TABLE t_row_col AS
SELECT id, 'c1'cn, c1 cvFROMt_col_rowUNION ALL
SELECT id, 'c2'cn, c2 cvFROMt_col_rowUNION ALL
SELECT id, 'c3' cn, c3 cv FROMt_col_row;SELECT * FROM t_row_col ORDER BY 1,2;
1)AGGREGATE FUNCTION ->适用范围:8i,9i,10g及以后版本
selectid ,max(decode(cn,'c1',cv,null)) ascl,max(decode(cn,'c2',cv,null)) asc2,max(decode(cn,'c3',cv,null)) asc3fromt_row_colgroup byidorder by 1
max 聚集函数也可以用sum,min,avg等其他聚集函数替代。
被指定的转置列只能有一列,但固定的列可以有多列,请看下面的例子:
select mgr,depton,ename from scott.emp order by 1,2;selectmgr,
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"fromscott.empwhere mgr in (7566,7698)and deptno in (20,30)group bymgr,deptnoorder by 1,2
这里转置列为empno,固定列为mgr,deptno。
还有一种行转列的方式,就是相同组中的行值变为单个列值,但转置的行值不变为列名:
参考来源:http://www.cnblogs.com/linjiqin/p/3148808.html