Re: SQL selection

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/05/04
Message-ID: <8erbe9$rdr$1@news3.isdnet.net>#1/1

You can use a function, something like that:

v734> create table tab (item varchar2(10), type varchar2(10)); ...
v734> create or replace function list ( field1 varchar2 )   2 return varchar2 as

  3     res   varchar2(2000) := null;
  4     first boolean := true;
  5  begin
  6     for curs in
  7         (select type from tab where item = field1 )
  8     loop
  9        if not first then
 10           res := res || ' ';
 11        else
 12           first := false;
 13        end if;
 14        res := res || curs.type;
 15     end loop;

 16 return res;
 17 end list;
 18 /

Function created.

v734> select * from tab;

ITEM TYPE
---------- ----------

FRUIT      APPLE
FRUIT      ORANGE
FRUIT      PEAR
FRUIT      PEACH
VEG        CARROT
VEG        PEA
VEG        POTATO

MINERAL SALT
MINERAL IRON
MINERAL COPPER
MINERAL ZINC

PLANET     VENUS
PLANET     SATURN
STAR       SUN

14 rows selected.

v734> select item, substr(list(item),1,50) types   2 from tab
  3 group by item
  4 /

ITEM TYPES

---------- --------------------------------------------------
FRUIT      APPLE ORANGE PEAR PEACH
MINERAL    SALT IRON COPPER ZINC
PLANET     VENUS SATURN
STAR       SUN
VEG        CARROT PEA POTATO

5 rows selected.

--
Have a nice day
Michel


<martincat_at_my-deja.com> a écrit dans le message : 8eq1g4$q45$1_at_nnrp1.deja.com...


> Say we have a database (oracle) with a this table

> that has two columns ITEM and TYPE where ITEM is

> one and TYPE can be many.

>

> ITEM TYPE

> FRUIT APPLE

> FRUIT ORANGE

> FRUIT PEAR

> FRUIT PEACH

> VEG CARROT

> VEG PEA

> VEG POTATO

> MINERAL SALT

> MINERAL IRON

> MINERAL COPPER

> MINERAL ZINC

> PLANET VENUS

> PLANET SATURN

> STAR SUN

>

> What I would like to do is to make a select that

> displays the data as follows:

> With the ITEM appearing on one line followed on

> the same line by each of the related TYPES.

> Note that the number of TYPES is variable and

> they do not need to appear in any special order.

>

> FRUIT APPLE ORANGE PEAR PEACH

> VEG CARROT PEA POTATO

> MINERAL SALT IRON COPPER ZINC

> PLANET VENUS SATURN

> STAR SUN

>

> What would be the most effective method to

> perform this?

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.

Received on Thu May 04 2000 - 00:00:00 CDT