Concat rows into single column


Imran> select * from student;     

 Sno

----------     

1 

2

3


Imran> select xmlagg (xmlelement (e,Sno ||',')).EXTRACT('//text()') as Sno from (select distinct Sno from student);

 Sno

--------------------------------------------------------------------------------------------------------------------------

1,2,3,

If we observe the above result it contains comma(,) at the end. In order to remove comma(,) we will use RTRIM function:

Imran> select rtrim(xmlagg (xmlelement (e,Sno ||',')).EXTRACT('//text()'), ',') as Sno from (select distinct Sno from student);

Sno

-------------------------------------------------------------------------------------------------------------------------

1,2,3



Comments

Popular posts from this blog

SQLNET.ORA Parameters: SQLNET.EXPIRE_TIME & SQLNET.INBOUND_CONNECT_TIMEOUT

Administering an Oracle Database Instance Using ORADIM

How to resolve "`/root/.gvfs':Permission denied" when running df command from non-root user