Re: Multiple Record Query

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 18 Jan 2002 22:04:05 -0600
Message-ID: <ud706vn03.fsf@rcn.com>

On 7 Jan 2002, nickheppleston_at_gmx.co.uk wrote:
> Any help would be much appreciated on the
> following query - i don't have a clue where to
> start!!!
>
> I have a table where multiple records have the
> same product part number.
>
> e.g.
>
> part_no description
> TM611TXV Line one of text,
> TM611TXV Line two of text,
> TM611TXV Line three of text,
> TM611TXV Line four of text,
>
> I want to take each record with the same part
> number and concatenate them into one record which
> is returned from the query.
>
> resulting in:
>
> part_no description TM611TXV Line one of text,
> Line two of text, Line three of text, Line four
> of text
>
>
> The only problem is that there could be 1 or 20+
> records that need to be concatenated - it needs
> to work it out on the fly.
>
> Thanks for your time on this matter, Nick

Tom Kyte has a nice write-up on this. You need PL/SQL. I'm taking his last example he wrote and adapting it to your situation, untested.

sqlplus> create or replace function get_full_description return varchar2
  2 is
  3 l_data varchar2(4000);
  4 begin

  5          for x in ( select description from your_table )
  6          loop
  7                  l_data := l_data || ',' || x.description;
  8          end loop;
  9  
 10          return ltrim( l_data, ',' );

 11 end;
 12 /

Function created.

sqlplus> select get_full_description from your_table

         where part_no = TM611TXV;

This should be able to retrieve it for you, I hope, but remember, untested.

---

BELOW IS THE CUT AND PASTED Q&A:

---

Tom,
Hope u have enjoyed yr vacation...We missed you ..Here is my question and i need help from you as usual..
I have a table "country" it will have two fields they are "id" and "name" it will have many records ...I need to display data of"name" of the countries in a row ..like

Name : USA,Canada,mexico,india,singapore,briton,..........etc in row or rows instead of
USA,
Canada
mexico
india
singapore
briton
..........

can u pl help

---

and we said...

If you know the maximum number of countries -- you can do this in SQL:

scott_at_ORA817DEV.US.ORACLE.COM> select rtrim( max( decode( rownum, 1, dname, null ) ) ||','||

  2                max( decode( rownum, 2, dname, null ) ) ||','||
  3                max( decode( rownum, 3, dname, null ) ) ||','||
  4                max( decode( rownum, 4, dname, null ) ) ||','||
  5                max( decode( rownum, 5, dname, null ) ) ||','||
  6                max( decode( rownum, 6, dname, null ) ), ',' )

  7 from dept
  8 /

RTRIM(MAX(DECODE(ROWNUM,1,DNAME,NULL))||','||MAX(DECODE(ROWNUM,2,DNAME,NULL))||', '||MAX(D

---

---

ACCOUNTING,RESEARCH,SALES,OPERATIONS Here, I know there are 6 or less dnames in my dept table -- it'll concatenate them together for me... If there were more then six -- they would silently be ignored

If I don't know the max, I'll use plsql instead in a function callable from sql

scott_at_ORA817DEV.US.ORACLE.COM> create or replace function get_dnames return varchar2
  2 is
  3 l_data varchar2(4000);
  4 begin

  5          for x in ( select dname from dept )
  6          loop
  7                  l_data := l_data || ',' || x.dname;
  8          end loop;
  9  
 10          return ltrim( l_data, ',' );

 11 end;
 12 /

Function created.

scott_at_ORA817DEV.US.ORACLE.COM>
scott_at_ORA817DEV.US.ORACLE.COM> select get_dnames from dual;

GET_DNAMES

---

---

ACCOUNTING,RESEARCH,SALES,OPERATIONS Note that regardless of method -- you are limited to 4000 characters in a VARCHAR2 in SQL -- you cannot go longer then that with that datatype.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Fri Jan 18 2002 - 22:04:05 CST