Oracle 改写listagg 4000 varchar 的限制的方法 clob

SQL> CREATE TYPE tab_varchar2 AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION concat_array(p tab_varchar2) RETURN CLOB IS
  2     l_result CLOB;
  3  BEGIN
  4     FOR cc IN (SELECT column_value FROM TABLE(p) ORDER BY column_value) LOOP
  5        l_result := l_result ||' '|| cc.column_value;
  6     END LOOP;
  7     return l_result;
  8  END;
  9  /

Function created.

SQL> SELECT item,
  2         concat_array(CAST (collect(attribute) AS tab_varchar2)) attributes
  3    FROM data
  4   GROUP BY item;

ITEM ATTRIBUTES
1    a b c
2    a c
3    a b



Any body know how to implement this on DB2 ?
posted @ 2016-11-23 14:33  yuerspring  阅读(849)  评论(0编辑  收藏  举报