Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2580
  • Last Modified:

concat multiple rows

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
akorn
Asked:
akorn
1 Solution
 
k_murli_krishnaCommented:
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
 
myexpert2002Commented:

   hi akorn,

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

--MyExper2002
0
 
DrSQLCommented:
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
 
NetminderCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now