COLLECT function in Oracle 10g
We can use COLLECT function in Oracle 10g to get the same result. This method requires a table type and a function to convert the contents of the table type to a string.
CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab,
p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2
IS
l_string VARCHAR2(32767);
BEGIN
FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
IF i != p_varchar2_tab.FIRST THEN
l_string := l_string
p_delimiter;
END IF;
l_string := l_string
p_varchar2_tab(i);
END LOOP;
RETURN l_string;
END tab_to_string;
The query below shows the COLLECT function in action.
SELECT deptno,
tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
3 rows selected.
LISTAGG Analytic Function in 11g Release 2
The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list.
COLUMN employees FORMAT A50
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
3 rows selected.
WM_CONCAT Built-in Function
If you are not running 11g Release 2, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you.
COLUMN employees FORMAT A50
SELECT deptno, WM_CONCATE(ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
Tuesday, March 23, 2010
Monday, December 7, 2009
jQuery-Enter key Form Submit
$(document).ready(function(){
$.datepicker.setDefaults({
showOn: 'both',
buttonImageOnly: true,
buttonText: 'Alt text for calbutton',
buttonImage: '/i/themes/sfa/jquery/smoothness/images/calendar.gif', // Calendar image
duration:'fast'
});
$(function() {
$("#P2_CALL_ST").datepicker();
$("#P2_CALL_END").datepicker();
});
// Enterkey Form Submit
$('#wwvFlowForm').keyup(function(e) {
if(e.keyCode == 13) {
//alert('Enter key was pressed.');
fnSearch();
}
});
});
$.datepicker.setDefaults({
showOn: 'both',
buttonImageOnly: true,
buttonText: 'Alt text for calbutton',
buttonImage: '/i/themes/sfa/jquery/smoothness/images/calendar.gif', // Calendar image
duration:'fast'
});
$(function() {
$("#P2_CALL_ST").datepicker();
$("#P2_CALL_END").datepicker();
});
// Enterkey Form Submit
$('#wwvFlowForm').keyup(function(e) {
if(e.keyCode == 13) {
//alert('Enter key was pressed.');
fnSearch();
}
});
});
Subscribe to:
Posts (Atom)