Solved

concat multiple rows

Posted on 2002-04-18
4
2,546 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
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 5 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

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.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

856 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