Aggregating Multiple Row values into One column

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

Comments

Post a Comment

Popular posts from this blog

Personal Contact Manager

Great ideas need not necessarily come in washrooms. I got this one while on bike with my friend Rajiv on our way to Shayak's place. Personal Contact Manager, a website that allows the user to manage his/her contacts in an easy and efficient manner. Now, whats different with it. A lot of applications provide this feature. Ever since Windows is being shipped by Microsoft, Phonebook and Outlook Express provide these features. So, let me put it this way. Where are most of the contacts of a person? Even if you don't have any answer or trying to juggle out your way counting the contacts at various social networking sites; even a layman would want to have all his contacts right on his hand. But there is already a mobile phone there. So where should the contact go... Yeah, you guessed it right. On the mobile phone. Wait a minute, we were talking about a website. Where does the mobile come into picture. It does. Let me put the things this way. The actual place where the contacts are sto
Read more

An apt quote

I feel like I am not anyone's first choice. Neither their favourite. Even if people tell me I'm important to them or I mean a lot to them, I know there is always someone they prefer to be with. Someone they chose over me. And that's hurt... a lot.
Read more

Alone

No one can see the pain that we hide, They're happy for us to keep it inside, Our fear is our own; they don't want to know, Why should we involve them; why should it show. You live your whole life in confusion and fear, The need to feel something unbearably near, Half of you living, Half of you gone, And inside you know what your doing is wrong. The thing's that can help, the thing's that may heal, Are the flame or the blade and the sting of the steel, The destruction of skin means the death of your soul, But there's nowhere to run when your living alone.
Read more