oracle高级SQL（四）--SQL行列转换专题2

浏览数：33 / 时间：2015年06月12日

【上一专题】行列转换1 -- PIVOT与UNPIVOT函数的介绍

实现案例

1001 lufei

1001 shanzhi

1001 namei

1001 qiaoba

实现效果如下：

1001 lufei,shanzhi,namei,qiaoba

典型的行列转换：实现这种方法有很多。

其中oracle11g之后提供了listagg函数;用来实现上面的效果最好不过了；我们来看下listagg语法

listagg函数的语法结构如下：

>listagg( [,]) within group (order by ) [over (partition by )]

listagg虽然是聚合函数，但可以提供分析功能（比如可选的OVER()子句）。

使用listagg中，下列中的元素是必须的：需要聚合的列或者表达式

WITH GROUP 关键词

分组中的ORDER BY子句

下面将演示listagg函数使用的例子

本文描述了在oracle 11g release 2 版本中新增的listagg函数，listagg是一个实现字符串聚合的oracle内建函数

先实现案例代码

--listagg方法  11g之后推荐使用这种
SELECT NO,
    listagg(EMP, ‘,‘) within GROUP (ORDER BY NULL) AS vals
FROM   onepiece
GROUP  BY NO ;
--wmsys.wm_concat方法 --11之前可使用这种
SELECT NO,
    WMSYS.WM_CONCAT(emp) AS vals
FROM   onepiece
GROUP  BY NO;
--使用集合函数；在用to_string函数将集合转换为字符串。需10g以上
SELECT NO,
    TO_STRING(CAST(COLLECT(emp) AS varchar2_ntt)) AS vals
FROM   onepiece
GROUP  BY NO;

上面我们看到listagg函数语法中有over函数；当然我们的listagg也可以做分析函数

SELECT NO,
    listagg(EMP, ‘,‘) within GROUP (ORDER BY NULL) over (partition by no)
FROM   onepiece

第3种实现的方法中有to_string函数；其中源代码如下：

CREATE OR REPLACE FUNCTION to_string
(
 nt_in        IN varchar2_ntt,
 delimiter_in IN VARCHAR2 DEFAULT ‘,‘
) RETURN VARCHAR2 IS
 v_idx PLS_INTEGER;
 v_str VARCHAR2(32767);
 v_dlm VARCHAR2(10);
BEGIN
 v_idx := nt_in.FIRST;
 WHILE v_idx IS NOT NULL
 LOOP
  v_str := v_str || v_dlm || nt_in(v_idx);
  v_dlm := delimiter_in;
  v_idx := nt_in.NEXT(v_idx);
 END LOOP;
 RETURN v_str;
END to_string;

最后：上面的效果反之呢？因为还有一个知识点没有讲解；暂时不提供。

作者： li0924

时间： 2015-01-12

本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

oracle高级SQL（四）--SQL行列转换专题2