kiranreddys.com
Kiran's personal website
Blog    Forums   Sitemap   Social    Phone
 
     

 
Knowledgebase
 
Home
Knowledge base home, and site home.
Categories
List of all Knowledge base categories.
Glossary
An alphabetical list of technical terms.
Contact Me
Contact me for knowledge base questions
My Blog
My articles, white papers, ideas and thoughts.
 

 
 


Multiple rows into single comma separated string.

Date Added January 1, 1970 | Print | Bookmark

--Convreting multiple rows in to single comma separated string without looping trough the cursor.
--Using SQL with Oracle SYS_CONNECT_BY_PATH to concatenate multiple rows in to single line.


SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(your_col, ', ')), ',') AS YOUR_COL
FROM (
SELECT 'X' AS X, t1.name_col AS YOUR_COL,
ROW_NUMBER () OVER (PARTITION BY 'X' ORDER BY ROWNUM) RN
FROM
table_1 t1,
table_2 t2,
WHERE
t1.id = t2.id
)
CONNECT BY 'X' = PRIOR 'X'
AND RN = PRIOR RN + 1
START WITH RN = 1

Was this article helpful?

Yes No

Category: Oracle

Last updated on January 1, 1970 with 5316 views

0 User Comments

There are no comments yet...Kick things off by filling out the form below.

Leave a Comment

CAPTCHA Image
Security code

 



Home | About Me | Products | Support | Contact | Knowledgebase | Forums | Blog | Site map | Site Search | Subscribe | Links | Social | News

Travel | Guest Book | All Pages | History | Site Calendar | Recipes | Ex-Rates | UK Stuff | Downloads | Telugu Stuff | Tell a Friend | Feedback

Bookmark and Share
www.kiranreddys.com
Terms of Use
Site contents Copyright © 2008-2009 Kiran Reddy. All rights reserved.