Re: Displaying query results as a single row....

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 7 Oct 1999 12:25:58 +0200
Message-ID: <7thsh5$omk$1@oceanite.cybercable.fr>

You can use a function like this:

create table clients (id number primary key, name varchar2(20));

insert into clients values (0, 'Client A');
insert into clients values (1, 'Client B');
insert into clients values (2, 'Client C');
create table rules (id number primary key, clientid number references clients,
                    name varchar2(20));
insert into rules values (0, 0, 'Rule 1');
insert into rules values (1, 0, 'Rule 2');
insert into rules values (2, 1, 'Rule 1');
insert into rules values (3, 2, 'Rule 1');

insert into rules values (4, 2, 'Rule 2'); insert into rules values (5, 2, 'Rule 3'); commit;

v734> create or replace function client_rules (clid number) return varchar   2 is

  3     res   varchar2(2000) := null;
  4     first boolean := true;
  5  begin
  6     for curs in
  7         (select name from rules where clientid = clid order by id )
  8     loop
  9        if not first then
 10           res := res || ', ';
 11        else
 12           first := false;
 13        end if;
 14        res := res || curs.name;
 15     end loop;

 16 return res;
 17 end client_rules;
 18 /

Function created.

v734>
v734> select name client, substr(client_rules(id),1,50) rules   2 from clients
  3 order by id
  4 /

CLIENT RULES

-------------------- --------------------------------------------------
Client A             Rule 1, Rule 2
Client B             Rule 1
Client C             Rule 1, Rule 2, Rule 3

3 rows selected.

--
Regards

Michel

Sam <naeem786_at_my-deja.com> a écrit dans le message : 7thp3l$caf$1_at_nnrp1.deja.com...
> Hi all,
>
> I hope the following makes sense......
>
> Is there any way in SQL*PLUS to display a number of rows returned by a
> query as a sinlge row/record?
>
> For example the following scenario (made very simple for explanation)
> where Client_Table has a one to many relationshuip with the
> Rules_table:
>
> Client_Table containes clients
> Client A
> Client B
> Client C
>
> Rules_Table containes rules for above clients
> Client A Rule 1
> Client A Rule 2
> Client B Rule 1
> Client C Rule 1
> Client C Rule 2
> Client C Rule 3
>
>
> In a simple join (say for client A) I will get:-
> Client A Rule 1
> Client A Rule 2
>
> WHAT I LIKE TO SEE RETURNED IS:-
>
> Client A Rule 1 Rule 2
>
> Thanks for your help.
>
> Naeem.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Oct 07 1999 - 05:25:58 CDT