[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

concat multiple rows

Posted on 2002-04-18
4
Medium Priority
?
2,566 Views
Last Modified: 2012-06-21
the following nested select returns more than one record.
are there any means to concat these into one string, so that "myconcat" can hold the result?

SELECT
  cust_name,
  (select category_name from vw_ord_cust_product where cust_id = 813629) myconcat
FROM
  customer a
WHERE
  a.cust_id = 813629


best regards
akorn
0
Comment
Question by:akorn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 6950157
The follwoing PL/SQL program should do it:

CREATE OR REPLACE PROCEDURE STRCNCT AS
myconcat varchar2(2000):= '';
cname varchar2(40);
ctgname varchar2(40);
CURSOR C1 IS SELECT CUST_NAME FROM CUSTOMER WHERE CUST_ID=813629;
CURSOR C2 IS SELECT CATEGORY_NAME FROM VW_ORD_CUST_PRODUCT where CUST_ID = 813629;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO cname;
EXIT WHEN C1%NOTFOUND;
OPEN C2;
LOOP
FETCH C2 INTO ctgname;
EXIT WHEN C2%NOTFOUND;
myconcat := myconcat || cname || ctgname;
END LOOP;
CLOSE C2;
END LOOP;
CLOSE C1;
DBMS_OUTPUT.PUT_LINE(myconcat);
END;

Put this in a file myconcat.sql in C drive and
SQL> @c:\myconcat.sql
SQL> set serveroutput on
SQL> exec strcnct;

DBMS_OUTPUT.PUT_LINE can display max 255 chars per line. So either you should display using substr function or a program in a loop to split into number of lines OR select myconcat into a table column and then query to get output in a spool file.

Just where DBMS_OUTPUT.PUT_LINE exists you can put insert into tabhold values(myconcat); where table tabhold has one column myconcat varchar2(2000).

If you want just category_name to be concatinated, remove cursor C1 altogether from the program. Good luck:)

--- k_murli_krishna
0
 

Accepted Solution

by:
myexpert2002 earned 20 total points
ID: 6950252

   hi akorn,

       what murali is said is absolutely right, by writing the procedure we can get it.

--MyExper2002
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6951038
akorn,
   Was this a mistake?  It sure seemed like k_murli gave the comment and myexpert2002 was just offering confirmation.  If this WAS a mistake, please consider posting a question on the Community Support topic (with zero points) and explain what happened.  Be sure to post the URL from this question.

Good luck!
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6953872
akorn,

I have now reduced the points in half, per our discussion in http://www.experts-exchange.com/jsp/qShow.jsp?ta=commspt&qid=20291007

You should now leave a "Points For" question in this Topic Area for the Expert you wish to reward.

Netminder
CS Moderator
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question