Re: Nonstandard result formatting

From: schaaph <schaaph_at_xs1.xs4all.nl>
Date: 25 Nov 1994 00:25:36 GMT
Message-ID: <3b3au1$55j_at_news.xs4all.nl>

Krzysztof Dorota (kdorota_at_kruuna.hut.fi) wrote:

: Hello SQL'ers!
 

: Does anyone have a solution for a following problem?
: I need to format a query result in a "nonstandard" way.
: Number of tables and rows can not be restricted by the query.
: I have access to Oracle 6 and 7 .
 

: Example:
: DEPT
: DEPTNO DNAME
: ---------------
: 10 ACCOUNTING
: 20 RESEARCH
: 30 SALES
: ===============
: EMP
: DEPTNO ENAME
: -------------
: 10 CLARK
: 10 KING
: 30 ALLEN
: 30 BLAKE
: ===============
 

: Desirable result:
 

: DEPTNO DNAME EMPLOYEES
: ------------------------------------
: 10 ACCOUNTING CLARK,KING,...
: 20 RESEARCH
: 30 SALES ALLEN,BLAKE,...
: .. ......... ............
: =====================================
: Any help will be appreciated
 

: Krzysztof
: --

I suppose you'll have to write a PL/SQL program like this

declare big_string char (some_big_number) ; begin
  for r_dept in c_dept loop

    big_string := rpad (to_char (r_dept.deptno), 10, ' ') ||
                  rpad (r_dept.dname, 30, ' ') ;
    for r_emp in c_emp (r_dept.deptno) loop
      big_string := big_string || r_emp.ename || ',' ;

    end loop ;
    big_string := substr (big_string, 1, length (big_string) - 1) ;     insert into results values (big_string) ;   end loop ;
end ; Received on Fri Nov 25 1994 - 01:25:36 CET