Re: SQL Query: Concatenation of multiple records

From: raghu <raghu1ra_at_rediffmail.com>
Date: 19 Dec 2001 10:05:54 -0800
Message-ID: <d11b9387.0112191005.711302a0@posting.google.com>

mark.powell_at_eds.com (Mark D Powell) wrote in message news:<178d2795.0112181326.413c7ca4_at_posting.google.com>...
> nickheppleston_at_gmx.co.uk (Nick Heppleston) wrote in message news:<150151d5.0112180327.6928b3ad_at_posting.google.com>...
> > Trying to solve a query which is completely beyond me. Any help would
> > be greatly appreciated.
> >
> > We have 2 tables, the first a product table with each record relating
> > to an individual product. The second table is a 'Long Description'
> > table with multiple entries per product code (relating to a unique ID
> > in the first table). So, for product '00ASD23' the long description is
> > broken down into say five different records each with the product code
> > '00ASD23'.
> >
> > The query needs to pull back individual records for each product (as
> > in this first table), however each record must also contain the long
> > description, made up from multiple records in the second table. These
> > records must be in essence concatenated with each other and returned
> > as one string that can be included into each individual product
> > record.
> >
> > Again, any help would be greatly appreciated. If you require more
> > information, please don't hesitate to contact me or reply to this
> > posting.
> >
> > Cheers, Nick
> > nick.heppleston_at_midwich.com
>
> 1- Use pl/sql as this would be easy as long as the total length of the
> descriptions does not exceed 32K (max. length varchar2 in plsql).
>
> 2- If you have to use pure sql then here is one possibility if the
> second table has a position indicator of some type on the order of the
> descriptions for a product key and the maximum number of descripts is
> small then you can do something like:
>
> select a.x, b.desc||c.desc||d,desc
> from a, b, c, d
> where a.key = b.key(+)
> and a.key = c.key(+)
> and a.key = d.key(+)
> and b.position = 1
> and c.position = 2
> and d.position = 3
>
> It seems like I know a better way to do this, but right now this is
> all that comes to mind.
>
> -- Mark D Powell --

hi this is from raghu ram

SQL> select * from t1;

CC DECP
------ ---------

1      a
2      b
3      d

SQL> select * from t2;

CC DECP
------ ---------

1      a
1      b
1      c
2      c
2      d

LET THE TWO TABLES YOU DESCRIBED IN THE POSTING BE T1 AND T2 AND THE DATA IS AS REQUIRED BY YOU
NOW THE OUTPUT YOU REQUESTED CAN ONLY BE OBTAINED BY THE FOLLWING CODE BUT NOT WITH A QUERY Declare
x t1%rowtype;
y t1.cc%type;
z t1.decp%type;
cursor c1 is select cc from t1;
cursor c2 (a t1.cc%type) is
select decp from t2 where cc=a;
k number;
b t1.decp%type;
begin
open c1;
select count(1) into k from t1;

	dbms_output.put_line('Code'||' Description');
	for i in 1..k loop
		fetch c1 into y;
		open c2(y);
	fetch c2 into b;
			if c2%found then
			close c2;
			open c2(y);
			z:=null;
				loop
				fetch c2 into b;
				exit when c2%notfound;
				z:=z||b;
				
				end loop;
			dbms_output.put_line(y||'    '||z);
			close c2;
			else
			dbms_output.put_line(y||'    '||'No product entry in table t2');
			close c2;
			end if;

	end loop;

end;

NOW IF WE EXECUTE THE ABOVE CODE THEN THE OUTPUT WILL AS FOLLOWS SQL> @grps.txt;
 37 /
Code Description
1 abc
2 cd
3 No product entry in table t2

PL/SQL procedure successfully completed.

SQL> spool off

if this works out please post a return message

certified DBA Received on Wed Dec 19 2001 - 12:05:54 CST