Concatenating Rows in a Table

By
Advertisement

Advertise Here

Though I work with Oracle DB for the past nine months religiously this is my first post anywhere related to oracle. Its high time , I thought to give some post on Oracle ,which are though not something which was never found by any other people but it was quite interesting for me when I first tried it. Though there are many virtuosos on oracle blogging around the world vigorously, I also wanted to be a small part on this big knowledge sharing base.
Concatenating the rows of the table will always be required when you are working with database. At those time the following query would be handy for you.
hari9ir2@GENEVART>create table t(
2 name varchar2(20));
Table created.
hari9ir2@GENEVART>
INSERT INTO t VALUES('Hari');1 row created.
hari9ir2@GENEVART>
INSERT INTO t VALUES('Haran');1 row created.
hari9ir2@GENEVART>
INSERT INTO t VALUES('Ragunathan');1 row created.
SELECT LTRIM(MAX(
SYS_CONNECT_BY_PATH(NAME , ' ')),' ')AS Full_Name
FROM (SELECT NAME , ROWNUM rn FROM t)
START WITH rn = 1
CONNECT BY PRIOR rn = rn -1;


FULL_NAME
------------------------
Hari Haran Ragunathan
To know more on the this refer this column in oracle magazine - On Favorites and Connect by .
Hope this tip could be useful for you.

0 comments:

Post a Comment

After reading the post , please leave your thoughts good / bad for me to help improve