oracle行转列

基础知识:

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 没有重复值
rank()OVER (PARTITION BY COL1 ORDER BY COL2) 有2个第2名时,接下来从第4号排序
dense_rank()OVER (PARTITION BY COL1 ORDER BY COL2) 有2个第2名时,接下来从第3号排序
例句

  select emp.deptno,emp.sal,emp.empno,row_number() over (partition by deptno order by sal desc) row_number,  --1,2,3    
   rank() over (partition by deptno order by sal desc) rank, --1,1,3    
   dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2

结果如下

  10    5000.00    7839    1    1    1    
  10    2450.00    7782    2    2    2    
  10    1300.00    7934    3    3    3    
  20    3000.00    7788    1    1    1    
  20    3000.00    7902    2    1    1    
  20    2975.00    7566    3    3    2    
  20    1100.00    7876    4    4    3    
  20    800.00    7369    5    5    4    
  30    2850.00    7698    1    1    1    
  30    1600.00    7499    2    2    2  

思路1
********转自:http://lyd8935.iteye.com/blog/178961******************************************
使用 WMSYS.WM_CONCAT 进行列转换
select  t.rank, t.Name from t_menu_item t;

    10 CLARK
    10 KING
    10 MILLER
    20 ADAMS
    20 FORD
    20 JONES
    20 SCOTT
    20 SMITH
    30 ALLEN
    30 BLAKE
    30 JAMES
    30 MARTIN
    30 TURNER
    30 WARD

--------------------------------
我们通过 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成 行转列的效果

    select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank;

DEPTNO ENAME
------ ----------
    10 CLARK, KING, MILLER
    20 ADAMS, FORD, JONES, SCOTT, SMITH
    30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
思路2
****************************转自:http://blog.csdn.net/youbl/archive/2009/09/18 /4565174.aspx


表结构和数据如下（表名Test）：

NO VALUE  NAME
1       a       测试1
1       b       测试2
1       c       测试3
1       d       测试4
2       e       测试5
4       f        测试6
4       g       测试7

Sql语句：
select No,
       ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value,
       ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name
  from (select No,
               Value,
               Name,
               rnFirst,
               lead(rnFirst) over(partition by No order by rnFirst) rnNext
          from (select a.No,
                       a.Value,
                       a.Name,
                       row_number() over(order by a.No, a.Value desc) rnFirst
                  from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
group by No;



检索结果如下：

NO VALUE    NAME
1    a;b;c;d   测试1;测试2;测试3;测试4
2    e            测试5
4    f;g          测试6;测试7

简单解释一下那个Sql吧：
1、最内层的Sql（即表tmpTable1），按No和Value排序，并列出行号：
select a.No,
       a.Value,
       a.Name,
       row_number() over(order by a.No, a.Value desc) rnFirst
  from Test a
该语句结果如下：
NO VALUE NAME RNFIRST
1     d       测试4     1
1     c       测试3     2
1     b       测试2     3
1     a       测试1     4
2     e       测试5     5
4     g       测试7     6
4     f       测试6     7


2、外层的Sql（即表tmpTable2），根据No分区，取出当前行对应的下一条记录的行号字段：
select No,
       Value,
       Name,
       rnFirst,
       lead(rnFirst) over(partition by No order by rnFirst) rnNext
  from (这里是tmpTable1的SQL) tmpTable1

lead(rnFirst)：取得下一行记录的rnFirst字段
over(partition by No order by rnFirst) 按rnFirst排序，并按No分区，分区就是如果下一行的No字段与当前行的No字段不相等时，不取下一行记录显示
该语句结果如下：
NO VALUE NAME RNFIRST RNNEXT
1     d        测试4     1         2
1     c        测试3      2         3
1     b        测试2     3         4
1     a        测试1     4         NULL
2     e        测试5     5         NULL
4     g        测试7     6         7
4     f         测试6     7         NULL


3、最后就是最外层的sys_connect_by_path函数与start递归了
sys_connect_by_path(Value, ';')
start with rnNext is null
connect by rnNext = prior rnFirst
这个大概意思就是从rnNext为null的那条记录开始，递归查找，
如果前一记录的rnFirst字段等于当前记录的rnNext字段，就把2条记录的Value用分号连接起来，
大家可以先试试下面这个没有Max和Group的Sql：
select No,
       sys_connect_by_path(Value, ';') as Value,
       sys_connect_by_path(Name, ';') as Name
  from (select No,
               Value,
               Name,
               rnFirst,
               lead(rnFirst) over(partition by No order by rnFirst) rnNext
          from (select a.No,
                       a.Value,
                       a.Name,
                       row_number() over(order by a.No, a.Value desc) rnFirst
                  from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst

结果是：
NO VALUE       NAME
1     ;a            ;测试1
1     ;a;b         ;测试1;测试2
1     ;a;b;c     ;测试1;测试2;测试3
1     ;a;b;c;d  ;测试1;测试2;测试3;测试4
2     ;e            ;测试5
4     ;f             ;测试6
4     ;f;g          ;测试6;测试7

可以看到，每个No的最后一条记录就是我们要的了
所以在sys_connect_by_path外面套一个Max，再加个Group by No，得到的结果就是行转列的结果了
最后再加一个Ltrim，去掉最前面的那个分号，完成.

评论

1 楼 贝特虫子 2013-07-25  

管用，谢谢！