Solved

concat multiple rows

Posted on 2002-04-18
4
2,518 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
4 Comments
 
LVL 17

Expert Comment

by:k_murli_krishna
Comment Utility
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 5 total points
Comment Utility

   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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now