The Lost Dragon

One thing that I surely believe is "Necessities drive Innovation". But, one has to think different at crucial times to be innovative. Well, that's not a subject of discussion for this. But it does originate from that idea. Just before our monthly release and when everything is all prepared, we found a bug in the code. GOD, how many times we test, where do they make their way. Anyways, we needed something which required no code changes as the code is already delivered. We only had database with us to play. Basically our problem was, we needed the first record from the set of two records based on a values in VARCHAR field. That means, we needed to apply some ordering to the records based on the values. But, ORDER BY only supports Alphabetic ordering on the values. But, that's what we generally know. Because we wanted to order the records in the manner we specify, we had to look for some solution. And we did find an interesting stuff at Jmatrix.net that allows us to s...

This is pretty interesting and useful. Sometimes, we might need to aggregate multiple row values into one single column. Oracle 9i provides xmlagg() function to achieve this. Here is the employee table example which aggregates the multiple employee names into one column based on the department no. select deptno, rtrim (xmlagg (xmlelement (e, ename ',')).extract ('//text()'), ',') enames from emp group by deptno ; DEPTNO ENAMES ---------- ---------------------------------------- 10 CLARK,MILLER,KING 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD Magic isn't it. For more details, refer to the following URL: http://www.dba-oracle.com/t_converting_rows_columns.htm