Solved

concat multiple rows

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 29 77
PL/SQL - Leading zeros 7 59
How to return an OUT parameter from and ORACLE 3 56
sort a spool into file output in oracle 1 22
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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, 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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

911 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

21 Experts available now in Live!

Get 1:1 Help Now