Re: SQL*PLUS question

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1995/11/06
Message-ID: <47lo0h$412_at_inet-nntp-gw-1.us.oracle.com>#1/1

Patrick Beyltjens <Patrick.Beyltjens_at_ping.be> wrote:

>hi there,
 

>I have a(litle) sqlproblem.
>suppose i have a table : proddate date,
> product char(10)
> production number(5,0)
 

>example :
 

>05-NOV-95 PROD1 10000
>06-NOV-95 PROD1 20000
>06-NOV-95 PROD2 30000

>I want to have output such as
 

>05-NOV-95 PROD1 10000
>06-NOV-95 PROD1 PROD2 30000
 
>with other words :
> The sum of the production AND the concatenation of the products
> grouped by date.
 

>I hope someone can help me
 

> despertly seeking patrick beyltjens
>
> email : Patrick.Beyltjens_at_ping.be

>

You didn't have a database version but if you are using 7.1 or above, the following example against scott.dept will show you how to do this easily.

First create a procedure like:

  1 create or replace function enames_for_dept( l_deptno in number )   2 return varchar2
  3 as
  4 return_string varchar2(2000);
  5 begin

  6     for x in ( select ename from emp where deptno = l_deptno )
  7     loop
  8        return_string := return_string || ' ' || x.ename;
  9     end loop;
 10     return ltrim( return_string );

 11* end enames_for_dept;
SQL> / then you can execute a query like:

SQL> l
  1 select deptno, enames_for_dept(deptno) enames, sum(sal)   2 from emp
  3* group by deptno
SQL> /  

    DEPTNO ENAMES                           SUM(SAL)
---------- ------------------------------ ----------
        10 CLARK KING MILLER                    9636
        20 SMITH JONES SCOTT ADAMS FORD        13596
        30 ALLEN WARD MARTIN BLAKE TURNER      10340
            JAMES


Replace my deptno with your proddate
        my enames_for_dept with your prod_name_by_date
        my sum(sal) with your sum(production)

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Mon Nov 06 1995 - 00:00:00 CET